Issue: 2, Remove BACKUP LOG WITH NO_LOG|TRUNCATE_ONLY statements from scripts
BACKUP LOG WITH TRUNCATE_ONLY is a dangerous command: it empties out the contents of your SQL Server’s transaction log without really backing it up. Database administrators sometimes run this command right before shrinking their log file with a DBCC SHRINKFILE command, thereby freeing up drive space.
WHY ‘TRUNCATE_ONLY’ IS NOT A RECOGNIZED BACKUP OPTION.
When you truncate transaction logs, you lose the ability to recover to a specific point in time. You shouldn’t be running this command except during extreme emergencies. Unfortunately, administrators started running it on a regularly scheduled basis, and then they got surprised when they couldn’t restore the way they wanted.
Microsoft recommends that instead of truncating logs, you switch to simple recovery mode instead. That way you don’t generate logs you won’t be using, and you won’t incur performance impacts from repeatedly filling and truncating the logs. You also remove the need to regularly back up the transaction log. This has plenty of drawbacks – if something goes wrong with your database, your only option will be to restore the previous full backup. You could lose hours – maybe even days – of data.
To stop people from shooting themselves in the foot, Microsoft removed this capability completely from SQL Server 2008. If you try to use this command:
BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY
You get an error:
?Msg 155, Level 15, State 1, Line 1 'TRUNCATE_ONLY' is not a recognized BACKUP option.
The only official workaround in SQL Server 2008 and newer is to switch the database’s recovery model to simple as shown in Books Online. This empties out the transaction log, thereby letting the DBA run a DBCC SHRINKFILE afterwards, then switch the recovery model back to full.
That solution still suffers from most of the same problems as using TRUNCATE_ONLY – the database’s recoverability is compromised. It’s just as bad of a solution, but unfortunately Microsoft can’t remove that workaround since we do need to put databases into simple recovery mode for other reasons.
HOW TO BACKUP LOG WITH TRUNCATE_ONLY IN SQL SERVER 2008
Don’t try what you’re about to see at home. We’re what you call experts. We’ve got years of experience that keeps us safe. Just like TRUNCATE_ONLY, this solution has a ton of drawbacks and compromises your recoverability. This should only be done in cases where a log has grown out of control and must be erased or else the system may crash. In any other situation, you should consider backing up the log with conventional means.
We can fake it by not writing our backup to a real device. SQL Server lets us use the NUL: location as a backup target, so the following will do a log backup without actually saving the contents anywhere:
BACKUP LOG MyDb TO DISK=’NUL:’
Remember, we’re still not fixing anything here: whatever caused the log file to grow in the first place can happen again and put us right back where we started.
Your data is not actually backed up. This is a giant problem. Don’t leave this script lying around where a junior DBA might see it and reuse it for regular backups. This should only be used to impress your friends with your useless knowledge of SQL Server, much like I’m doing here.
Other Common Questions About Transaction Log Backups
Q: Why shouldn’t I shrink log files?
A: When SQL Server needs to grow the log file back out, it’s a blocking operation. Everything in the database is put on hold while the log file grows out. We can avoid this for data files by using Instant File Initialization, but that doesn’t take effect for log files.
Q: But I had a one-time log file growth and I swear it’ll never need to grow that big again.
A: Okay, cool – go ahead and shrink the log file this once, but make sure you leave enough log file space for normal operations.
Q: How big should the transaction log be for normal operations?
I generally start at 25% of the data file size. If you plan on rebuilding your indexes, the log needs to be large enough to hold the size of your largest object, plus space for transactions that are happening during your index rebuilds. If your database is dominated by a single large object, then it might need to be bigger than 25%. Plus, if you’re using things like replication, mirroring, or AlwaysOn Availability Groups, you’ll need enough log space to hang on to transactions during replica downtime – until that replica comes back up and can download the rest of the transactions it missed during the outage.
Q: What can I do to keep transaction log requirements low?
Check out Kendra Little’s webcast on The Developer And The SQL Server Log where she explains the physical structure of the transaction log and how you can aim for minimal logging.
Q: What are the biggest mistakes people make with the log file?
Watch Brent Ozar’s video DBA Darwin Awards: Log File Edition where he talks about automatically shrinking your logs, ignoring Virtual Log Files (VLFs), and bulk inserts.
Q: How can I find out if Virtual Log Files (VLFs) are a problem for me?
Run our free sp_Blitz®, a health check stored procedure that catches databases with abnormal VLFs, bad growth configurations, and much more.