Best practices: Truncate Trans log during backup.
Posted on 2005-04-08
Just wanted to get some feedback from the community on the best way to accomplish the task of truncating the transaction log on my SQL2k DBs during our nightly dumps..
Right now was have several nightly scheduled jobs that look pretty much like below:
BACKUP DATABASE [MYDATABASE] TO DISK = N'D:\SQL2K\MSSQL\BACKUP\MyDatabase_bk.bak' WITH INIT , NOUNLOAD , NAME = N'MyDB backup daily', NOSKIP , STATS = 10, NOFORMAT
Obviously this doesn't truncate the log. Using SQL's builtin maintenance plan wizard has been unreliable since v6.5, and I refuse to use it.
Would the best was to truncate the log nightly be to add a second step in the scheduled job that does it only if the backup was successful?
Maybe something like:
backup log MyDatabase with truncate_only
DBCC SHRINKFILE(MyDatabase_Log, 2)
Though I've been told the using DBCC is pretty sloppy..
Suggestions, Comments all appreciated..