The transaction log file records all transactions and the database modifications made by each transaction. Heavy application processing against the database can make the transaction log grow quickly. Therefore, the transaction log must be truncated on a ADHOC basis to keep it from filling up.
Each physical log file is divided into smaller logical units called virtual log files (VLFs). Truncation provides free space by deleting inactive virtual log files. To avoid running out of log file space or physical disk space if growth is set to unlimited, one should understand when a truncation occurs.
If the recovery model is set to simple, a truncation happens after a checkpoint. Under the full or bulk-logged recovery model, a truncation occurs if a log backup is proceeded by a backup and a checkpoint. Automatic database checkpoints occur when the server’s target recovery time is zero, the default. This process happens every 1 minute.
I will be using continuing with the MATH database that contains all prime number between 2 and 6 million. The log file has grown from 32 MB to 458 MB since no regular maintenance has been performed.
We can try reducing the transaction log file by using the DBCC SHRINKFILE command. However, this does not work.
|
1 2 3 |
-- Try to shrink the log DBCC SHRINKFILE(MATH_ALL_LOG, 32); GO |
I wonder if there are any open transactions that are causing our shrink command to fail? Using the DBCC OPENTRAN command, we can see there are no open transactions.
|
1 2 3 |
-- Make sure there are not open transactions DBCC OPENTRAN; GO |
We want to look at the actual composition of the transaction log file (VLFs), by using the undocumented DBCC LOGINFO command.
|
1 2 3 |
-- Size of virtual log files DBCC LOGINFO; GO |
At this point, we find the root cause of our problem. The log file pointer is at the end of the file. Because the point is the active point of the log, the DBCC SHRINKFILE command will not work.
To move the log file point to be beginning of the file, we can create a temporary table and make a bunch of modifications to the first record. Since all these modifications will be recorded in the transaction log file, the pointer will move to the front of the circular natured log.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- Create temp table and move pointer DECLARE @VAR_CNT INT; SELECT @VAR_CNT = 1; CREATE TABLE [dbo].[TMP_LOGPTR] (MYVAL INT); INSERT INTO [dbo].[TMP_LOGPTR] VALUES (@VAR_CNT); WHILE (@VAR_CNT < 5000) BEGIN UPDATE [dbo].[TMP_LOGPTR] SET MYVAL = MYVAL + 1; SET @VAR_CNT = @VAR_CNT + 1 END DROP TABLE [dbo].[TMP_LOGPTR] GO |
Taking another look at the transaction log file details, we see that the log file pointer is at the front of the file but the end of the file has one VLF in use. To have a truncation to be performed, we need to execute the simple BACKUP LOG command.
|
1 2 3 |
-- Backup the log file BACKUP LOG [MATH] TO DISK = 'C:\MSSQL\BACKUP\MATH.BAK'; </span> |
Looking at the transaction log file details, we can see that all VLF’s except for the active one are clear.
We now can execute the DBCC SHRINKFILE and get a successful reduction in size.
Last but not least, we will use the following T-SQL snippet to review the transaction log usage, transaction log size and VLF allocation. The DBCC SQLPERF command reports log file space usage.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Show all database DBCC SQLPERF(LOGSPACE); GO -- Size of virtual log files DBCC LOGINFO; GO -- Log file size in pages SELECT * FROM sys.database_files WHERE file_id = 2 GO |
In summary, there are many factors that can delay log truncation. You should look at the following books online article for more details. The above process will alleviate the issue of an active pointer at the end of a transaction log file.
