In my earlier blog post on Shrinking the Transaction Log files in SQL Server, I had mentioned that a VLF can be truncated only when all transactions, that have at least one command written into the specified VLF have either been committed or rolled back.
While this is true, I understand that it might create a bit of confusion around how the truncation actually happens behind the covers. In his comment, David writes: Also, I have read in the documentation (Transaction Log Physical Architecture) that truncation of the log marks any VLFs as inactive "whose [log] records all appear in front of the ... MinLSN". Shouldn't that continue on and say "or after the last-written log record"? Or does "in front of" include the tail-end VLFs? Please excuse me, I am having a hard time "wrapping my mind around" the logical log.
This post is essentially to help David, and many more of my readers understand the logic and the under-the-covers steps that SQL Server does while truncating a Transaction Log File. Hence, I will try and demonstrate, with examples, a Log File Truncation.
For demonstration purposes, let us create a new database, and make sure that the database is in FULL recovery model. Why Full Recovery Model – because in Full Recovery Model, truncation will happen only after we take a log backup, and hence, will be completely under our control. Lets first create a blank new database using the script below:
CREATE DATABASE TLogTruncationDemoON PRIMARY ( NAME = 'TLogTruncationDemo_Data', FILENAME = 'F:\TLogTruncationDemo\TLogTruncationDemo_Data.mdf', SIZE = 10 MB, MAXSIZE = 100 MB )LOG ON ( NAME = 'TLogTruncationDemo_Log', FILENAME = 'F:\TLogTruncationDemo\TLogTruncationDemo_Log.ldf', SIZE = 2 MB, /* Set the MAXSIZE = SIZE so that the T-Log does not grow. */ MAXSIZE = 2 MB )GO
Now, before we proceed any further, let us change the Recovery Model of the database to Full, and then lets take a Full Backup, so that the Recovery Model change takes effect.
ALTER DATABASE TLogTruncationDemo SET RECOVERY FULLGOBACKUP DATABASE TLogTruncationDemo TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_Backup_01.bak'GO
Now, execute the following command:
CREATE TABLE TblTLogDEMO ( C1 INT IDENTITY(1, 1) NOT NULL, C2 INT NOT NULL, C3 VARCHAR(10) NOT NULL )GOINSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')INSERT INTO TblTLogDEMO (C2, C3) VALUES (2, 'A')INSERT INTO TblTLogDEMO (C2, C3) VALUES (3, 'A')GOCHECKPOINTGO
Current LSN Operation Context Transaction ID----------------------- --------------- --------- --------------00000014:0000006e:009e LOP_BEGIN_CKPT LCX_NULL 0000:0000000000000014:000000af:0001 LOP_END_CKPT LCX_NULL 0000:00000000
00000014:0000006e:009e is the LSN when the CHECKPOINT started and it ended at LSN 00000014:000000af:0001. Behind the covers, at LSN 00000014:000000af:0001, i.e., at LOP_END_CKPT, SQL Server also records another LSN, which is called the Minimum Recovery LSN (MinLSN).
The Minimum Recovery LSN (MinLSN) is the LSN of the first log record that must be present for a successful database-wide rollback. The MinLSN is the minimum of the:
At this point, since we do not have replication configured, and we do not have any active transaction either, the MinLSN will be equal to the LSN of the start of the CHECKPOINT process, i.e., 00000014:0000006e:009e.
EXPERIMENT 1 :: COMMIT, CHECKPOINT, BACKUP LOG
We will perform the following activities:
BEGIN TRAN Demo_Tran_01GOINSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')GO 3050COMMIT TRAN Demo_Tran_01GOCHECKPOINTGO
Current LSN Operation Context Transaction ID----------------------- --------------- --------- --------------00000015:000000eb:0007 LOP_BEGIN_CKPT LCX_NULL 0000:0000000000000015:000000ee:0001 LOP_END_CKPT LCX_NULL 0000:00000000
At this point, we can clearly understand that at this stage, all the LSNs in VLF 1 are less than the MinLSN, while VLF 2 has at least 2 LSN that are either equal to or greater than the MinLSN.
When a Transaction Log File is truncated, all entries having “Current LSN” less than the MinLSN are deleted. Later, any VLF, that becomes completely empty is marked as Inactive. In other words, VLFs that consist only of LSNs less than the MinLSN are marked as Inactive and Truncated.
In our case, VLF 1 consists only of LSNs less than the MinLSN, and hence only VLF 1 should be truncated. To verify this, lets take a Log Backup of the database using the command below:
BACKUP LOG TLogTruncationDemo TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_LogBackup_01.trn'GO
EXPERIMENT 2 :: CHECKPOINT, BACKUP LOG, COMMIT
In the second part of the exercise, let us begin a transaction and then continue inserting records, so that the commands completely fill up VLF 2 and moves on into VLF 3. Later, we will fire a manual CHECKPOINT. We use the script below:
BEGIN TRAN Demo_Tran_02GOINSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')GO 3000CHECKPOINTGO
Now, when we fire the CHECKPOINT, there is an active transaction, that started at LSN 00000015:000000ef:0002 (LOP_BEGIN_XACT), while the LSN corresponding to the last LOP_BEGIN_CKPT is 00000016:00000100:00d7. Since, 00000015:000000ef:0002 (LOP_BEGIN_XACT) is less than 00000016:00000100:00d7 (LOP_BEGIN_CKPT), the MinLSN will be equal to 00000015:000000ef:0002, which is the LSN of the start of the active transaction.
Current LSN Operation Context Transaction ID----------------------- ---------------- --------- --------------00000015:000000ef:0002 LOP_BEGIN_XACT LCX_NULL 0000:0000023900000016:00000100:00d7 LOP_BEGIN_CKPT LCX_NULL 0000:0000000000000016:00000123:0001 LOP_XACT_CKPT LCX_NULL 0000:0000000000000016:00000123:0002 LOP_END_CKPT LCX_NULL 0000:00000000
Also, 00000015:000000ef:0002 (LOP_BEGIN_XACT) is recorded in VLF 2 and the LSN of the CHECKPOINT end (LOP_END_CKPT), 00000016:00000123:0002, is recorded in VLF 3. So, now, both VLF 2 and VLF 3 have LSN entries greater than the MinLSN; and hence a Log Backup should not be able to truncate the VLFs. Lets verify:
BACKUP LOG TLogTruncationDemo TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_LogBackup_02.trn'GO
Now, commit the transaction and then manually fire a CHECKPOINT. Take a Log Backup, and check the status of the log once again:
COMMIT TRAN Demo_Tran_02GOCHECKPOINTGOBACKUP LOG TLogTruncationDemo TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_LogBackup_03.trn'GODBCC LOGINFO(TLogTruncationDemo)GO
EXPERIMENT 3 :: CHECKPOINT, COMMIT, BACKUP LOG
In this experiment, lets begin a new transaction and insert some rows, so that the entries start from VLF 3 and continue into VLF 4. The commands and the result is as below:
At this point, the MinLSN is the again the begin LSN of the start of the transaction Demo_Tran_03. This is because, it is the minimum of the starting LSN of the active transaction and the starting LSN of the last CHEKCPOINT process. Now, if we commit the transaction, and then take a Log Backup, what do we expect?
According to our theory, since both VLF 3 & 4 contain entries that are greater than the MinLSN, both should remain active, isn’t it? Lets check…
What’s going on? Actually behind the covers, whenever you take a backup (Full, Differential or Log Backup), SQL Server fires a CHECKPOINT again. This CHECKPOINT, obviously, comes after the Commit of the Transaction, and hence, the MinLSN for this CHECKPOINT is equal to the starting LSN of the CHECKPOINT. Now, things fall in place, right?
EXPERIMENT 4 :: WRAPPING AROUND THE LOG FILE
Now, let us execute the following queries:
BEGIN TRAN Demo_Tran_04GOINSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')GO 5000COMMIT TRAN Demo_Tran_04GODBCC LOGINFO(TLogTruncationDemo)GOBACKUP LOG TLogTruncationDemo TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_LogBackup_05.trn'GODBCC LOGINFO(TLogTruncationDemo)GO
We can see here that the Log File has wrapped around. How do we know? We know that the transactions have wrapped around the T-Log File by examining the FSeqNo column in the DBCC LOGINFO output. Till the end of Experiment 3, the FSeqNo column for VLF 1 had a value of 20, and now, it has been changed to 24, i.e., FSeqNo for VLF 4 + 1. This shows us that this VLF is being used after VLF 4 was full.
Hence, when in the article Transaction Log Physical Architecture, we say “Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN)”, what we essentially mean is:
While truncating the Transaction Log, we delete all records with “Current LSN” less than the MinLSN. At this point, any VLF that becomes completely empty, i.e., VLFs that consists only of LSN entries less than the MinLSN, is marked as free (inactive).
Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.
Thank you for taking the time to explain this here and in the last post. In the past I was never sure why my efforts to shrink a log file on a full recovery model database did not work. Sometimes it just seemed that I needed to repeat a log backup a few times first. Although I would like to step through this second post for myself, I believe that I understand what is going on now. I am also in a better position to question the many recommendations available on the web, such as “switch to the simple recovery model, shrink, switch back to the full model and then take a database backup”.
I have a follow-up question on best practice for shrinking. With a full recovery model database, I can see that a log backup and then SHRINKFILE is a minimum requirement. But should I do a CHECKPOINT beforehand to reduce the size of the active log? Also is a log backup with TRUNCATE_ONLY acceptable in replacement of a real log backup?
In any case I hope other DBAs discover these posts on shrinking the transaction log. Thank you!
Thanks for the comments.
To start off, I would like to mention that SHRINK-ing the T-Log is not a recommended practise, specially on production systems, unless the T-Log has grown suddenly after a heavy operation. Growing a database file is always a costly operation and should be avoided as much as possible.
To answer your questions, manual CHECKPOINT is not necessary. SQL Server should automate the CHECKPOINT process based on the recovery interval mentioned in sp_configure (unless you are hitting the bug as mentioned in KB909369 [http://support.microsoft.com/kb/909369]).
Moreover, we recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure. Use manual log truncation in only very special circumstances, and create backups of the data immediately.
Also, this option has been removed in SQL Server 2008. Avoid using it in new development work, and plan to modify applications that currently use it.Ref: http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx
Yes, shrinking the log should be a rare operation. A DBA should either 1) find out what the normal size should be and leave it that size, 2) find out why the log file grew too large to begin with and fix the real problem, 3) buy a larger disk, or add another disk. But sometimes -- you are pushed into doing something you would rather not do.
TRUNCATE_ONLY was another one of those recommendations I had seen on the web and thought I had better ask about. But as you pointed out, it is clearly discussed in the documentation.
Also, I appreciate your engineering/experimental approach to answering questions, such as this post.
Thank you for your posts on TLOG Truncation. They have been very helpful.
I have a situation where a DB's TLOG is in the SIMPLE recovery model, yet will not truncate. I ran DBCC OpenTran which showed that there were no Active transactions. Since the TLOG was not truncating automatically, I ran the CHECKPOINT statement as well as BACKUP LOG WITH TRUNCATE_ONLY, but the log would not truncate. Replication is not set up on this system. Also at this point, the TLOG had filled up the drive it was located on.
Q: Is the DBCC OpenTran command the correct indicator of whether it is possible for the VLF's to be truncated? I believe that I met all of the criteria you described in your previous post for a SIMPLE log to be truncated.
Thank you for any help in advance.
To answer your question, an Open Transaction is not the only cause of not being able to truncate the Transaction Log. please execute the query:
SELECT recovery_model_desc, log_reuse_wait_desc FROM sys.databases where name = '<your database name>'?
This will tell you why the Log File cannot be reused. If there is an active transaction, the log_reuse_wait_desc will show "ACTIVE_TRANSACTION".
Also, run the command DBCC LOGINFO(<your database name>) to see the status of the VLFs.
Please let me know if you have issues understanding the result set of these queries.
Suhas, We have not had our situation return (TLOG not truncating) since your post, so I have not been able to see the reason. The 'log_reuse_wait_desc' column will be very helpful for this situation.
Thank you again for your reply and the insightful article.
You made this so easy to understand and implement.
Especially thanks for clearing the popular DBA myth of 'Truncating T-log file before shrinking'
Keep the good work
If we use dbcc log(dbname, 3), we can find a column named "Minimum LSN" in LOP_END_CKPT row. The value of "Minimum LSN" is always equal to LOP_BEGIN_CKPT's Current LSN.
Is "Minimum LSN" just same as MinLSN in this artical?If so, why does "Minimum LSN" not equal to the oldest transaction's LSN?
The Minimum Recovery LSN (MinLSN) that I have discussed about in the article is not the same as "Minimum LSN" as you have indicated. The "Minimum LSN" indicated by you will always be equal to the "Current LSN" of LOP_BEGIN_CKPT.
The Minimum Recovery LSN (MinLSN) is the LSN of the first log record that must be present for a successful database-wide rollback. If log records above LOP_BEGIN_CKPT are not available, recovery of this database will fail and the database will move into SUSPECT mode.
For your information, if you notice the output of the command, if you have open transactions when CHECKPOINT gets invoked, an additinal row is written into the Transaction Log (apart from LOP_BEGIN_CKPT and LOP_END_CKPT). The Operation is called "LOP_XACT_CKPT". If you look at the description of this row, it will list the Transaction IDs of all active transactions when CHECKPOINT was invoked.
In a sample test I did, the Description said: "XdesIDs: 0000:00397329,0000:0039732a"; this means that there are 2 open transactions with Transaction IDs 0000:00397329 and 0000:0039732a.
Obviously, 0000:00397329 is less than 0000:0039732a and hence 0000:00397329 is the Oldest Active Transaction. During recovery, SQL Server will look for a record with "Operation" = "LOP_BEGIN_XACT" and "Transaction ID" = "0000:00397329" and will start recovery from this LSN. The "Current LSN" of this record is what I have referred to MinLSN or Minimum Recovery LSN.
Hope this helps!!