I realize that there are some cases where, in an emergency, you need to shrink a log file because it took over the drive. In SQL Server 2005 and earlier, you could get out of the jam quickly, by issuing a BACKUP LOG WITH TRUNCATE_ONLY (followed by DBCC SHRINKFILE). In SQL Server 2008, this "feature" within BACKUP LOG is no longer available, and with good reason; however, there are other – shall we say, "clever" – ways to achieve this. But too many people pass this advice off casually, and this gives people the impression that it's okay to change their recovery model from full to simple and then back to full, in order to allow them to shrink their log file(s). This article, for example, is getting "high fives" from other sites, even though it is giving terrible advice (IMHO) without any context whatsoever:
How about explaining WHY the command has been deprecated? Too many people were completely shooting themselves in the foot by using this command and destroying their backup chain, possibly not even knowing they had done so, just so they could reclaim a few GB of disk space (in almost all cases, temporarily!).
How about explaining WHY they might need to use this code? SQL Server is very good at managing its own log file(s). If your log file expands and fills up your disk, you are doing at least one thing wrong:
- you are in full recovery mode, but you are not taking log backups often enough (or at all);
- you are in full recovery mode, but should be in simple, because you do not need point-in-time recovery (usually this is true for Dev/Test/QA systems, but not production);
- you had a very bizarre and atypical log-expanding transaction; or,
- you did not plan correctly for your log and recovery needs.
How about showing them how to do disaster recovery right in the first place? Picking the right recovery model, determining tolerance for data loss and downtime, making sure to not just back up to the local disk and leave it at that, sizing the log files appropriately, testing recovery plans, etc. There is a lot more to it than just creating a database, and then reacting to a full disk by shrinking the log file.
I strongly recommend that before taking code samples like this and running off and "fixing" all of your disk space issues, you read these articles from like-minded SQL Server professionals:
I'd also recommend spending some time looking through the Recovery Models Overview in Books Online.