April 7, 2008 at 6:51 am
Disclaimer: I know this is old hat for many of you but I am a novice ...
(1). When using a DBCC SHRINKFILE statement to recover physical disk space after BACKUP LOG statement, do the following two statements do the same thing:
DBCC SHRINKFILE (N'Database_Log')
DBCC SHRINKFILE (Database_Log,X) where X is the target size, if specified
I can find no description anywhere for the syntax of the first statement anywhere but it appears to work.
(2). I read that when index optimization is selected in a Maintenance Plan that the DBCC REINDEX statement is run and that it creates many more entries in the log file than a DBCC INDEXDEFRAG statement would.
I currently run the maintenance plan once a week and it expands the log file to approximately the same size as the database .mdf file itself.
Any suggestions for a better way?
April 7, 2008 at 7:04 am
1: without the size parameter the DBCC SHRINKFILE will use the size specified when the file was created, or the last size set by using ALTER DATABASE (copy from DBCC SHRINKFILE (on BOL 2005)
2: consider switching to bulk recovery mode for the index rebuilding.
Regards,
Andras
April 7, 2008 at 7:08 am
Phil Auer (4/7/2008)
...(2). I read that when index optimization is selected in a Maintenance Plan that the DBCC REINDEX statement is run and that it creates many more entries in the log file than a DBCC INDEXDEFRAG statement would.
...
Also read http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
It describes it better than I could duplicate the same info here.
(especially the part: Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG )
Regards,
Andras
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply