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.

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 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.

Update 8/14/2008 - Paul Randal wrote a good blog post on what to do when your transaction log fills up.

Technorati Tags: backup log, truncate, truncate_only