question

bruno5885 avatar image
bruno5885 asked

Shrinkfile then backup truncate_only then shrinkfile? But why?

SQL Newbie here. I've inherited some older SQL Servers at my new job. I've been checking out their current maintenance plans and came across the following: USE IC3I GO Dbcc shrinkfile ('ic3i_log', 2) Backup log ic3i with truncate_only Dbcc shrinkfile ('ic3i_log', 2) Dbcc shrinkfile ('emercall_log', 2) Backup log emercall with truncate_only Dbcc shrinkfile ('emercall_log', 2) Can someone tell me why there's a DBCC shrinkfile before and after the Backup? And I've been reading that the outdated command of "Backup log ____ with truncate_only" is dangerous to use. Thoughts?
backupdbccsql server 2005
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered
The truncate_only option was officially deprecated with SQL Server 2008, so that command won't work with that version or newer. I have no idea why the first shrink is there, but the combination of the other 2 steps would empty the log and then immediately shrink it (to almost nothing). In my opinion, that makes absolutely no sense unless you don't care about the log for recovery anyway. And if you don't care about things like transaction log point-in-time restore, then just switch the database to simple recovery model. If the database is already using that model, and you want to shrink the log after a special one-time or infrequent operation that made it grow a lot, just use the shrinkfile by itself. The other isn't necessary because simple recovery model essential tells the engine you never care about the log except during transaction processing. Once the transaction (implicit or explicit) is committed, you never need to see that part of the log again. I hope that answers your question. Please let us know if you have further questions.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Like Tom said, change to simple recovery mode and the empty log will happen automagically. Shrinkfile would take the size of the logfile down to a small size. That would make sense if you rarely every do something which would require a large transaction log (like a large Atomic load of data), and other Days you only have small transactions. But that's a very rare situation. More often, you have large transactions in your system running frequently and then you should avoid shrinking the logfile (because it would require an expensive grow operation every time a large transaction is running). If you really know large transactions are rare, you should carefully select your autogrow optios, so that they are set to a reasonably large fixed size and not a percentage of the current size. I would suggest keeping the jobs for a while and monitor your system (look eg at grow and shrink operations) in order to find out how often and how large your grow operations are and how often they take Place. When you have some metrics, you can decide if the shrink operations are necessary or not, and if you should switch to simple recovery or not.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.