July 11, 2011 at 6:14 am
I have implemented the following Database Weekly Maintenenance Job. We are running SQL Server 2005 EE 64-bit SP3 on Windows Server 2003 R2 EE (64-bit). I would like to know about the order of the Trucate Log and ShrinkDatabase Tasks. As you can see, I have the ShrinkDatabase Command after the Truncate Log/Shrink Log Commands. However, I noticed my log file is larger after the ShrinkDatabase Command. Should I rearrange the commands and put the ShrinkDatabase Command before the Truncate Log Command? Please note any other change I could implement to make this a better weekly maintenance job (while at the same time keeping it simple.)
Thanks, Kevin
PRINT ''
PRINT '***********************************************************'
PRINT '* BACKUP BB_BB60 - BEFORE:'
PRINT '***********************************************************'
PRINT ''
GO
DECLARE @TimeStamp char(17)
SET @TimeStamp = (SELECT REPLACE(CONVERT(varchar(10),getdate(),112),'/','')) + (SELECT REPLACE(CONVERT(varchar(12),getdate(),108),':',''))
DECLARE @FileName char(75)
SET @FileName = '\\bbbackup\Maintenance\bb_bb60_before_' + Substring(@TimeStamp,1,12) + '.bak'
BACKUP DATABASE bb_bb60 TO
DISK= @FileName
GO
PRINT ''
PRINT '***********************************************************'
PRINT '* DBCC DBREINDEX:'
PRINT '***********************************************************'
PRINT ''
GO
DBCC DBREINDEX('ACTIVITY_ACCUMULATOR','',70)
DBCC DBREINDEX('__x_qti_result_data','',70)
DBCC DBREINDEX('eud_item_recipient','',70)
DBCC DBREINDEX('QTI_RESULT_DATA','',70)
DBCC DBREINDEX('user_msg_state','',70)
DBCC DBREINDEX('__x_qti_asi_data','',70)
DBCC DBREINDEX('X_FILES','',70)
DBCC DBREINDEX('FILES','',70)
DBCC DBREINDEX('COURSE_ASSESSMENT','',70)
DBCC DBREINDEX('COURSE_MAIN','',70)
DBCC DBREINDEX('USERS','',70)
DBCC DBREINDEX('QTI_ASI_DATA','',70)
GO
PRINT ''
PRINT '***********************************************************'
PRINT '* BB_BB60 TRUNCATE LOG:'
PRINT '***********************************************************'
PRINT ''
GO
BACKUP LOG bb_bb60 WITH TRUNCATE_ONLY;
GO
PRINT ''
PRINT '***********************************************************'
PRINT '* DBCC SHRINKFILE LOG:'
PRINT '***********************************************************'
PRINT ''
GO
DBCC SHRINKFILE (bb_bb60_log, 50);
GO
PRINT ''
PRINT '***********************************************************'
PRINT '* DBCC SHRINKDATABASE:'
PRINT '***********************************************************'
PRINT ''
GO
DBCC SHRINKDATABASE (bb_bb60, 25)
GO
PRINT ''
PRINT '***********************************************************'
PRINT '* BACKUP BB_BB60 - AFTER:'
PRINT '***********************************************************'
PRINT ''
GO
DECLARE @TimeStamp char(17)
SET @TimeStamp = (SELECT REPLACE(CONVERT(varchar(10),getdate(),112),'/','')) + (SELECT REPLACE(CONVERT(varchar(12),getdate(),108),':',''))
DECLARE @FileName char(75)
SET @FileName = '\\bbbackup\Maintenance\bb_bb60_after_' + Substring(@TimeStamp,1,12) + '.bak'
BACKUP DATABASE bb_bb60 TO
DISK= @FileName
GO
July 11, 2011 at 6:16 am
you should change your plan to remove the Shrink DB and log tasks all together, it is not good practice to regularly Shrink either.
set up t-log backups to manage the size of the t-log
July 11, 2011 at 6:25 am
Second the motion on getting rid of both steps. They kill performance, break your backup chain (no point-in-time recovery), and cause physical file fragmentation on the hard drive(s). Nothing good about them, just harmful.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2011 at 8:26 am
kevinsql7 (7/11/2011)
However, I noticed my log file is larger after the ShrinkDatabase Command. Should I rearrange the commands and put the ShrinkDatabase Command before the Truncate Log Command? Please note any other change I could implement to make this a better weekly maintenance job (while at the same time keeping it simple.)
Quite honestly you should remove them both. A regular shrink is very harmful (do you know you need to rebuild all your indexes afterwards?) and truncating the log is ruining any attempt at point-in-time recovery for this DB.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
Please read through this - Managing Transaction Logs[/url]
and http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply