Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 Aug 17 '09 at 12:04

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

up vote 36 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.


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 '14 at 23:08

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.

share|improve this answer

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

People often have a misconception about the full backup and log backups. In order for the backup to work in FULL backup recovery model, the t-logs must be used, as during the backups there may still be transactions going on in the database (unless you perform a so called COLD backup when you shut down the database). Oracle uses the same concept when you have a database in ARCHIVELOG mode. The sequence of a backup boils down to this:

  1. Start backup - suspend all actions in real files and write to t-logs.
  2. Perform backup - all transactions continue, but are not written to real files, they are written into t-logs
  3. End backup - resume writing database transactions to real files.
  4. If necessary flush what's in the T-logs into the real files.

That is the reason why t-logs are not by default truncated/shrinked, as they are a vital part of transaction continuation during the backup phase.

share|improve this answer

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

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

protected by Sven Jan 19 at 13:50

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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