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
January 14th, 2009 at 5:59 am
Brilliant article. I quite like the analogy of being caught in a bear trap – it shouldn’t happen in the first palce.
April 6th, 2009 at 10:51 am
In SQL 2008 this feature has been discontinued so even MS has seen the light.
BACKUP LOG WITH TRUNCATE_ONLY
None. The transaction log is automatically truncated when the database is using the simple recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model.
BACKUP LOG WITH NO_LOG
None. The transaction log is automatically truncated when the database is using the simple recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model.
http://msdn.microsoft.com/en-us/library/ms144262.aspx
Also remember dump load and backup transaction no longer exist in 2008 and that tape support is been removed in the next version.
August 21st, 2009 at 10:01 am
[...] blogged about why backup log with truncate_only is like a bear trap, and I meant it: you shouldn’t be runningthis command except during extreme [...]
October 15th, 2009 at 12:06 am
SharePoint Backup and Restore Strategies WebCast Deck, with Q and A…
Thanks for attending the Webcast. We had a great crowd with ……