0

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?

3 Answers 3

9

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

1
0

Where did you read about truncating the transaction log? It is complete rubbish.

If you do truncate the log then you'll actually invalidate the restores of any log backups. You can the only restore to your last FULL backup. This is why there is no option in the Maintenance Plan designer or in the BACKUP LOG commands.

There are some rare case when you truncate the log: in some DR cases in case of disk failure, or if you've filled a disk. Otherwise, it is the sheer idiocy.

-2

Set your databases to simple recovery or do a full nightly backup and clear the logs without losing information.

Here is the MS article on How to stop the transaction log of a SQL Server database from growing unexpectedly

4
  • 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
    – gbn
    Feb 28, 2011 at 15:33
  • Sorry, been a while since I did it, editing post. Also to note not everyone needs transaction logs on all databases.
    – pablo
    Feb 28, 2011 at 17:43
  • you always need a transaction log. Did you mean "transaction log" backup?
    – gbn
    Feb 28, 2011 at 17:57
  • here is an example of why you might use simple recovery: support.microsoft.com/kb/929870.
    – pablo
    Feb 28, 2011 at 18:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .