Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

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

share|improve this answer
    
Spot on: sqlskills.com/BLOGS/PAUL/post/… –  Holocryptic Feb 28 '11 at 17:56
add comment

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.

share|improve this answer
add comment

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

http://support.microsoft.com/kb/873235

share|improve this answer
    
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: support.microsoft.com/kb/929870. –  pablo Feb 28 '11 at 18:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.