How can I get Transaction Logs to auto-truncate after Backup

Question

Setup: Sql Server 2008 R2, databases set up with Full recovery mode.

I have set up a maintenance plan that backs up the transaction logs for a number of databases on the server. It is set to create backup files in sub-directories for each database, verify backup integrity is turned on, and backup compression is used. The job is set to run once every 2 hours during business hours (8am-6pm).

I have tested the job and it runs fine, creates the log backup files as it should. However, from what I have read, once the transaction log is backed up, it should be ok to truncate the transaction log. I do not see any option for doing this in the Sql Server Maintenance Plan designer. How can I set this up?

Solution

Strictly speaking, the log backup is truncating the portion of the log that's just been backed up. However, truncating the log simply marks the log entries for reuse - it doesn't imply that the physical log file will shrink. That would be a separate (manual) step, and really, if your log files needed to grow to that size in the first place, then repeatedly shrinking/growing them will only add to fragmentation and hurt performance.

Summary: If you're doing regular log backups, then you don't need to worry about manually truncating the logs (and if you do it manually anyway, you'll ruin your log backup chain).

Comments

[gbn]

You mean "Simple" recovery to have no log backups, No suchthing as "quick" recovery. And a full backup does not "clear the logs". -1 for misinformation

[pablo]

Sorry, been a while since I did it, editing post. Also to note not everyone needs transaction logs on all databases.

[Holocryptic]

Spot on: sqlskills.com/BLOGS/PAUL/post/…

[gbn]

you always need a transaction log. Did you mean "transaction log" backup?

[pablo]

here is an example of why you might use simple recovery: support.microsoft.com/kb/929870.