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.

I've just read through a lot of MSDN documentation and I think I understand the different recovery models and the concept of a backup chain. I still have one question:

Does a full database backup truncate the transaction log (using full recovery mode)?

  • If yes: Where is this mentioned in the MSDN? All I could find was that only BACKUP LOG truncates the log.

  • If no: Why? Since a full database backup starts a new backup chain, what's the point in keeping the transactions that were finshed before the full backup active in the log?

share|improve this question

migrated from stackoverflow.com Aug 17 '09 at 12:04

This question came from our site for professional and enthusiast programmers.

add comment

5 Answers

up vote 28 down vote accepted

Nope - it definitely doesn't. The only thing that allows the log to clear/truncate in the FULL or BULK_LOGGED recovery models is a log backup - no exceptions. I had this argument a while back and posted a long and detailed blog post with an explanation and a script that you can use to prove it to yourself at Misconceptions around the log and log backups: how to convince yourself.

Feel free to follow up with more questions. Btw - also see the long article I wrote for TechNet Magazine on Understanding Logging and Recovery in SQL Server.

Thanks

share|improve this answer
    
Thank you very much sir for your SUPER ANSWER and the article it has answered a million questions in my mind. –  M.Ali Jan 22 at 23:08
add comment

Basicaly you don't need to make shrink of transaction log automaticaly every time because transaction logs need space to work and if you truncate automaticaly it will stay almost the same size.

share|improve this answer
add comment

Dont confuse truncating the log with shrinking the log.

  • To TRUNCATE is to remove the transactions in the log that are before the last checkpoint, (the checkpoint being when transactions are flushed to the database itself). This is done using the BACKUP command.

  • To SHRINK the log is to reduce the actual log file size. This is done using DBCC commands.

share|improve this answer
add comment

From my understanding the only thing that truncates the transaction log is a log backup.

A full backup only copies enough of the log so that it is transactionally consistent, because it takes a while for the backup operation to complete & in that time, the pages copied may have changed.

You still need your log backups for point in time recovery.

I don't have MSDN to link to, but i can link you to Paul Randal's blog, who was a developer on the SQL Server team, wrote DBCC CHECKDB and parts of Books Online.

He also answers questions on this forum, so that would be an even better authority then 2nd/3rd hand information from me :)

share|improve this answer
add comment

A full backup does NOT truncate the log, you must perform a backup log operation. A full backup does NOT re-set the log chain -- that would totally screw up replication/log shipping, etc.

You'd have to look closely at how SQL Server does backups but know that in-flight/long running transactions are not included in the backup (otherwise the backup may never complete) so it's not quite accurate to say that a full backup of an online-database is guaranteed to make the next log backup obsolete.

http://msdn.microsoft.com/en-us/library/ms175477.aspx

share|improve this answer
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.