Share via

Full Backups & Transaction Logs Backup

  • Article

 

A general misconception when it comes to transaction log management, is that the transaction log gets automatically TRUNCATED (underlined, as I am not saying SHRINKED!!!) when a full database backup is performed.

 

This is completely untrue, and if you just don't believe it, just open Management Studio, right click your DB, select task  > backup, select backup full and then switch to options... well, as you can see, the "truncate the transaction log" checkbox is greyed out - not an option - indeed.

 

*

 

Obviously, TRUNCATING the transaction log is something you must do as a DBA, as if log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files.

 

So question is WHEN does it get TRUNCATED?

 

Well, except when delayed for some reason, log truncation occurs automatically as follows:

 

  • Under the simple recovery model, after a checkpoint.
  • -
  • Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.

 

For more info:

Transaction Log Truncation

https://msdn.microsoft.com/en-us/library/ms189085.aspx

 

Therefore, if you are under full of bulk logged recovery mode - you have two options:

  • Backup the transaction log - so that it will get automatically truncated (STRONGLY suggested)
  • Force the truncation (...)

 

Now when you actually go for the STRONGLY suggested option :-) i.e. you backup your transaction log and it is actually automatically truncated, this has actually a good consequence, which is let's say that your last backup is corrupted, you can still go back to the one before and recover all the way through to your favourite point in time :) yay!

 

OK now, what's the difference between TRUNCATE and SHRINK (https://msdn.microsoft.com/en-us/library/ms178037.aspx)? Think it like a box full of books. Truncate takes out the books but the size of the box is still the same, ready for new books (same amount) to be stored in. Shrink makes the box smaller and then when you actually have to store new books well, you will have less storage space - and then you will have to autogrow - therefore you'll be hitting performances (that's actually very good idea, I would like so much an autogrow luggage for my trips).

 

- Beatrice Nicolini with the contribution of Norm Eberly- aka "he"- Senior PFE -

Comments

  • Anonymous
    December 29, 2013
    Pingback from Useful URLs for SQL DBAs – Foundation | MS SQL Sense
  • Anonymous
    July 24, 2014
    nice
  • Anonymous
    December 15, 2015
    The comment has been removed
  • Anonymous
    January 19, 2016
    I would say that transaction log backup is truncate the transaction log. Note, that you can apply transaction log backup only if your database run under the full or bulk-logged recovery model. If you use simple recovery model, the transaction log will be truncated after each full backup. You can find more information about backups here -http://sqlbak.com/academy