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?

Yaakov Ellis
  • 556
  • 1
  • 10
  • 15

3 Answers3

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

db2
  • 2,180
  • 3
  • 15
  • 19
  • Spot on: http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-More-on-the-circular-nature-of-the-log.aspx – Holocryptic Feb 28 '11 at 17:56
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.

gbn
  • 6,079
  • 1
  • 18
  • 21
-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

StackzOfZtuff
  • 1,842
  • 13
  • 21
pablo
  • 3,040
  • 1
  • 19
  • 23
  • 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 '11 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 '11 at 17:43
  • you always need a transaction log. Did you mean "transaction log" backup? – gbn Feb 28 '11 at 17:57
  • here is an example of why you might use simple recovery: http://support.microsoft.com/kb/929870. – pablo Feb 28 '11 at 18:01