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.