Backup Log with Truncate_Only: Like a Bear Trap
We got a question about the best practices way to truncate their SQL Server log files, and that kinda scares me.
It’s somewhat akin to asking, “What’s the best way to cut my hand off to free myself from this bear trap before I starve to death in the wilderness?” Well, you shouldn’t be sticking your hand in bear traps to begin with, but if you find your hand in a bear trap, ANY way to get out of it is a good way. Pocket knife, teeth, band saw, whatever it takes. (Now there’s a sunny image to start the day.)
When people ask about truncating their log files, what they usually mean is, “We don’t have enough space on our log drive, and I need to free it up often.” This happens in systems where folks didn’t get buy enough space for their log files, and they have to repeatedly truncate their logs or else their system will run out of space.
How To Avoid Truncating Your SQL Server Logs with Truncate_Only
If we’re talking best practices, then really the answer is not to truncate the logs, period. Avoid that by:
- Increasing the size of the log drive. This is the easy answer, and it’s the best long-term answer, but sometimes we don’t have the money to do this, or there’s no space left for drives in the server or the SAN.
- Add an additional drive for logs, and either move some of the database log files there or add secondary log files on those drives.
- If the transaction logs truly don’t matter, then change the databases to simple recovery mode so they don’t need to be backed up. The log files won’t keep growing (unless there’s a monster transaction).
- Do transaction log backups more frequently (maybe we’re doing t-logs every hour, and need to be done every 15 minutes). I’m not wild about this answer because at some point, your log backups will fail for some reason (like your backup drive ran out of space) and your log drive will still fill up.
If You Have To Truncate Log Files with BACKUP LOG with Truncate_Only
If you do find yourself in a bear trap with a full transaction log drive, then the fastest way to get out of it is to run a query manually via T-SQL – forget LiteSpeed. Just use the native command:
BACKUP LOG [DatabaseName] WITH TRUNCATE_ONLY
However, like cutting your hand off, that has some really nasty implications – you lose your ability to do a point-in-time restore once you do that. Do a full backup as soon as possible afterwards, and then resume normal transaction log backups.
Free Up SQL Server Drive Space Without Using BACKUP LOG with Truncate_Only
After truncating the logs, you may have a huge log file – and you need to get rid of it to free up drive space. Run a DBCC SHRINKFILE command to shrink the file that’s grown too large.
Related Wiki Articles About Transaction Logs, Backups and Truncate_Only
- SQLServerPedia Wiki article on Backup Log with Truncate_Only by Mike Walsh
- Recovery Models – explains the difference between Full, Simple and Bulk Logged.
- SQL Server Backup & Restore Basics – a tutorial about SQL Server backup options.
- Filegroup Restore Tutorial Video – shows how to back up and restore individual filegroups in a database in SQL Server 2005 and 2008.
Tags: backup log, truncate, truncate_only
