41

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?

Alex Angas
  • 2,007
  • 2
  • 26
  • 37
Heinzi
  • 2,138
  • 5
  • 30
  • 51

6 Answers6

43

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

Lucero
  • 103
  • 5
Paul Randal
  • 7,184
  • 1
  • 35
  • 45
  • 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
13

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

Matt Rogish
  • 1,512
  • 6
  • 25
  • 41
8

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

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
5

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.

peterh
  • 4,914
  • 13
  • 29
  • 44
Peter
  • 51
  • 1
  • 1
1

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.

1

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.