Oh, the horror! Please stop telling people they should shrink their log files!
If you've gotten yourself in this situation, then one of the following cases is extremely likely:
- Your database is in full recovery mode, and it should really be in simple mode
- Your database is in full recovery mode, and you should be taking regular log backups
- Your database is in full recovery mode, and your log backups are failing for some reason
- You are running massively huge transactions that are blowing the log file up to massive sizes
The answer for each of these is as follows:
If (1), then switch the database to simple mode
If (2), then schedule regular log backups
If (3), then fix your scheduled log backups
If (4), then just don't do that :) Instead, do work in smaller batches.
Note that NONE of these require the use of the (deprecated) "backup log dbname with truncate_only"
Instead, once you do clear the log file using one of the above techniques, then shrink the (now empty) log with:
DBCC SHRINKFILE ('log logical name', 2000)
Always specify a reasonable final size, otherwise it'll shrink down to nearly 0, and the next time it is needed, will have to take the time to grow.
Best Answer
(I originally asked as a regular question but then found out the correct method - thanks BrentO)
No, never.
I've come across this several times now on ServerFault and want to reach a nice wide audience with some good advice. If people frown on this way of doing things, downvote and I'll remove this gladly.
Auto-shrink is a very common database setting to have enabled. It seems like a good idea - remove the extra space from the database. There are lots of 'involuntary DBAs' out there (think TFS, SharePoint, BizTalk, or just regular old SQL Server) who may not know that auto-shrink is positively evil.
While at Microsoft I used to own the SQL Server Storage Engine and tried to remove the auto-shrink feature, but it had to stay for backwards compatibility.
Why is auto-shrink so bad?
The database is likely to just grow again, so why shrink it?
I did a blog post a while back that has an example SQL script that shows the problems it causes and explains in a bit more detail. See Auto-shrink – turn it OFF! (no advertising or junk like that on my blog). Don't get this confused with shrinking the log file, which is useful and necessary on occasion.
So do yourselves a favor - look in your database settings and turn off auto-shrink. You should also not have shrink in your maintenance plans, for exactly the same reason. Spread the word to your colleagues.
Edit: I should add this, reminded by the second answer - there's common misconception that interrupting a shrink operation can cause corruption. No it won't. I used to own the shrink code in SQL Server - it rolls back the current page move that it's doing if interrupted.
Hope this helps!