My Field Job gave me this observation that many Customers have this misconception that whenever a Full Database backup is taken for a database in Full Recovery mode, it truncates the Tlog and hence breaks the Tlog backup chain which is so untrue. In this post I am going to clarify the same

One of the command which can help you to see whether your T-Log is truncated or not is “DBCC LOGINFO”. (Further Reading: http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-More-on-the-circular-nature-of-the-log.aspx)

The output of DBCC LOGINFO is as follows

DBCC LOGINFO(‘<database-name>’)

FileId      FileSize             StartOffset          FSeqNo      Status      Parity CreateLSN
———– ——————– ——————– ———– ———– —— —————————————
2           253952               8192                 194                 0           64     0
2           262144               262144              197                 2           64     0
2           262144               524288              195                 0           64     186000000012800007
2           262144               786432              196                 0           64     187000000014400003

Ideally DBCC LOGINFO gives the information on the physical structure of logfile viz number of VLFs in TLog, StartOffset of the VLF and the active VLFs.

However for this discussion, our area of interest in the above output is Status column. If we observe status column in the above output we see that one of the VLFs have Status 2 while other have Status 0. Status 2 represents Active VLFs which cannot be truncated while Status 0 represent inactive VLFs which can be used by sql server to write Transaction entries

If you periodically fire DBCC LOGINFO for the database which is in Full Recovery model, you will observe that as transactions are performed on the database, gradually all the VLFs in the output will have a Status 2, in other words they are active and cannot be truncated. The output will be as shown below

FileId      FileSize             StartOffset          FSeqNo    Status      Parity CreateLSN
———– ——————– ——————– ——————- —— —————————————
2           253952               8192                  593            2           64     0
2           253952               262144               594          2           64     0
2           270336               516096               595          2           64     17000000018400027
2           262144               786432               596          2           128    33000000019600050
2           262144               1048576              597         2           64     54000000029200017
2           262144               1310722              598         2           64     54000000045300016
2           262144               1572864              599         2           64     55000000018700026
2           262144               1835008              602         2           64     55000000036600008
2           262144               2097152              601         2           64     56000000005800103
2           262144               2359296              602         2           128    56000000028100024
2           262144               2621442              603         2           128    56000000046100012
2           327682               2883584              604         2           64     159000000024000002
2           327682               3211264              605         2           128    182000000025600284

 

However once you take backup of the Tlog file, the Status column of most of the VLF will become 0 except few (or atleast 1) depending upon whether they have Active transaction. The output of DBCC LOGINFO after taking the Tlog backup will be as shown below

FileId      FileSize             StartOffset          FSeqNo      Status      Parity CreateLSN
———– ——————– ——————– ———– ———– —— —————————————
2            253952               8192                 593         2           64     0
2           253952               262144               594         0           64     0
2           270336               516096               595         0           64     17000000018400027
2           262144               786432               596         0           128    33000000019600050
2           262144               1048576              597         0           64     54000000029200017
2           262144               1310720              598         0           64     54000000045300016
2           262144               1572864              599         0           64     55000000018700026
2           262144               1835008              600         0           64     55000000036600008
2           262144               2097152              601         0           64     56000000005800103
2           262144               2359296              602         0           128    56000000028100024
2           262144               2621440              603         0           128    56000000046100012
2           327680               2883584              604         0           64     159000000024000002

So, In other words taking the backup of the Tlog of the database in Full Recovery model, truncates the Tlog file. Also in Full Recovery model every Tlog backup following the Full Database Backup forms a Tlog backup chain should be maintained in order to completely recover the database.

However when you take a Full Database backup, it does not truncate the Tlog. So if you take the Full Database backup and does not capture Tlog backup, the Tlog doesn’t get trucated which could be easily verified from the output of DBCC LOGINFO after capturing the FULL DB Backup and it is as shown.

FileId      FileSize             StartOffset          FSeqNo    Status      Parity CreateLSN
———– ——————– ——————– ——————- —— —————————————
2           253952               8192                  593            2           64     0
2           253952               262144               594          2           64     0
2           270336               516096               595          2           64     17000000018400027
2           262144               786432               596          2           128    33000000019600050
2           262144               1048576              597         2           64     54000000029200017
2           262144               1310722              598         2           64     54000000045300016
2           262144               1572864              599         2           64     55000000018700026
2           262144               1835008              602         2           64     55000000036600008
2           262144               2097152              601         2           64     56000000005800103
2           262144               2359296              602         2           128    56000000028100024
2           262144               2621442              603         2           128    56000000046100012
2           327682               2883584              604         2           64     159000000024000002
2           327682               3211264              605         2           128    182000000025600284

 

Implications
========

1) So if you have Backup policy wherein you capture daily Full DB backups and periodic Tlog backups (say 15 mins) within a day. And if you loose the latest Full Database Backup but have old Full Database Backup alongwith the entire Tlog backup chain starting that Old Database Backup, we can completely recover the database but restoring the Old Database backup and applying all the Tlog backup chain since then.

(If you want to know how this can be done refer to blog from one of my colleagues http://blogs.msdn.com/b/sqlserverfaq/archive/2010/08/26/transaction-log-backup-and-restore-sequence-myths-amp-truths.aspx)

2) If you intend to Shrink the Database and you are under the impression that Full DB backup will truncate the Tlog file which you can further shrink then you might be wrong. In this case you have to take Tlog backup which is truncate the Tlog backup which in turn will allow you to shrink the Tlog file.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *