Shrinking a SQL Server Log File Multiple Times

Question

Recently ran into a situation where after a log backup and shrinkfile, the LDF file remained the same size. DBCC LOGINFO clearly showed only one active VLF and SSMS said that 99% of the space in the LDF was free. The first attempt did not shrink nor did the second attempt or the third. After the fourth attempt, success, the log was reduced to the requested size. Meanwhile, DBCC LOGINFO was saying that after every SHRINKFILE, another VLF became active.

I decided to run a test of a new database.

CREATE DATABASE logs_test
USE logs_test
-- first look at the VLFs for the logs_test database
DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 19 2 64 0
2 253952 262144 0 0 0 0
*/
-- put the database into FULL recovery before making a backup
ALTER DATABASE logs_test SET RECOVERY FULL
-- first look at the VLFs for the logs_test database
DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 19 2 64 0
2 253952 262144 0 0 0 0
*/
BACKUP DATABASE logs_test TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\logs_test_250503.bak'
WITH INIT
GO
DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 60 2 128 0
2 253952 262144 24 0 64 0
*/
-- create a table to put stuff in
CREATE TABLE newtable(a int);
GO
INSERT INTO newtable VALUES(10);
INSERT INTO newtable VALUES(20);
INSERT INTO newtable VALUES(30);
GO
-- run the update script again, the log file has grown to accomodate the log records
SET NOCOUNT ON
DECLARE @counter int
SET @counter = 1;
WHILE @counter < 100000 BEGIN
 UPDATE newtable SET a = a + 1
 SET @counter = @counter + 1;
END;
DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 23 2 64 0
2 253952 262144 24 2 64 0
2 270336 516096 25 2 64 24000000013400005
2 262144 786432 26 2 64 25000000016700003
2 262144 1048576 27 2 64 26000000015000005
2 262144 1310720 28 2 64 27000000015000005
*/

So far so good, VLFs were added and they have a status of 2. I run a BACKUP LOG

BACKUP LOG logs_test TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\logs_test_250503.trn'
WITH INIT
GO

Then I ran DBCC LOGINFO , there was one active VLF, exactly what I was expecting

DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 2162688 92930048 145 0 64 141000000347800005
2 2359296 95092736 146 0 64 145000000386300003
2 2359296 97452032 147 0 64 145000000386300003
2 2359296 99811328 148 0 64 145000000386300003
2 2424832 102170624 149 2 64 145000000386300003
*/

So now I run my shrinkfile command thinking that I will be left with a LDF of 1MB

DBCC SHRINKFILE (N'logs_test_log' , 1)
DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 109 2 128 0
2 253952 262144 97 0 64 0
2 270336 516096 98 0 128 61000000013500003
2 262144 786432 99 0 128 62000000016600005
2 262144 1048576 100 0 128 63000000015100003
2 262144 1310720 101 0 128 64000000015000005
2 262144 1572864 102 0 128 65000000015100003
2 262144 1835008 103 0 128 66000000015000005
2 262144 2097152 104 0 128 67000000015100003
2 262144 2359296 105 0 128 68000000015000005
2 262144 2621440 106 0 128 69000000015100003
2 327680 2883584 107 0 128 70000000015100003
2 327680 3211264 108 2 128 71000000027800005
*/

No luck, the LDF is still at 100MB and now there are two active VLFs

So I run shrinkfile again because the amount of free space has not changed 99%

DBCC SHRINKFILE (N'logs_test_log' , 1)
DBCC LOGINFO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 109 2 128 0
2 253952 262144 110 2 128 0
2 270336 516096 111 2 64 61000000013500003
2 262144 786432 99 0 128 62000000016600005
2 262144 1048576 100 0 128 63000000015100003
2 262144 1310720 101 0 128 64000000015000005
2 262144 1572864 102 0 128 65000000015100003
2 262144 1835008 103 0 128 66000000015000005
2 262144 2097152 104 0 128 67000000015100003
2 262144 2359296 105 0 128 68000000015000005
2 262144 2621440 106 0 128 69000000015100003
2 327680 2883584 107 0 128 70000000015100003
2 327680 3211264 108 2 128 71000000027800005
*/

Still no luck, the LDF is still at 100MB and now there are four active vlfs

So I run shrinkfile one last time and this is what I get

DBCC SHRINKFILE (N'logs_test_log' , 1)
DBCC LOGINFO
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 150 2 128 0
2 253952 262144 151 2 128 0
2 270336 516096 152 2 128 20000000013400005
2 262144 786432 153 2 128 21000000016600005
2 262144 1048576 154 2 128 22000000015000005
2 262144 1310720 155 2 128 23000000015100003
2 262144 1572864 25 0 64 24000000015000005

6 active VLFs and a LDF file at 1MB

My questions are this.

1. Why was it necessary to run SHRINKFILE more than one time to shrink the LDF?
2. With the LDF at 1MB, why do I still have so many VLFs showing up in DBCC LOGINFO?
3. Is it possible to know what is being stored within each VLF? I have the LSN but would like to know what operation took place.

Thank you

Update 15:41

Here is what I found on Reading log content

Thank you all for your input.

Answer 1

Next time, run two checkpoints before trying to shrink the file (assuming simple recovery) or take two log backups (assuming full or bulk-logged). This will force the log to wrap around to the beginning of the file, allowing the shrinkfile operation to eradicate most of the file. I think the fact that the 4th operation was successful was just by coincidence (perhaps multiple checkpoints happened in the meantime).

That said, why on earth would you want a 1 MB log file? If it grew once, it will grow again. Freeing up the space temporarily just so that it can autogrow during real activity (which blocks, because the growth of the log has to be zero-initialized, even with instant file initialization) seems like an exercise in futility. Why not just size the log appropriately, so that you don't have to deal with growths (ideally) or shrinks (wasteful).

Please have a good thorough read of this question and its answers:

Why Does the Transaction Log Keep Growing or Run Out of Space?

Further reading that might be more useful than trying to summarize:

• Remus Rusanu : How to shrink the SQL Server log
• Kimberly Tripp : Transaction Log VLFs – too many or too few?
• Paul Randal : Understanding Logging and Recovery
• Glenn Berry : Experiments with VLFs Part 1
• Paul Randal : Inside the Storage Engine: More on the circular nature of the log

• user4659: Thank you for the answer. The 1MB log file was just for the test above. For the real database in question, it was more like 30GB.

Answer 2

When you use DBCC SHRINKFILE(Logfile, size) it only truncates from the end of the log file back as far as it can go. When it reaches the highest virtual log still in use, it cannot shrink further. This is described in the SQL Server Books Online at:

http://technet.microsoft.com/en-us/library/ms189493.aspx

So, once the high end of the log is clear, it can be shrunk down in size. Again, that will depend on how much of the log is still in use. The log can be cleared by backups, but the backups will not clear incomplete transactions, so the log can remain in a high-end VLF even after repeated backups.

With regard to the increase and decrease of VLFs, how big was the log file created to be originally and what is the setting for log file growth? If it grows by only a small amount it will create more VLFs than anyone desires.

A common pattern for shrinking a log file is CHECKPOINT, BACKUP, SHRINKFILE, CHECKPOINT, BACKUP, SHRINKFILE, etc until you get results. There are many reasons that the log may not be shrinkable, including a very large rollback. (Not your current problem, of course.)

• user4659: Thank you for the explanation. Still would like to see what operations, if any are being stored in the LDF.
• RLF: You can use the fn_dblog() function to examine the log file. You can work through trial and error, but you might find this article interesting: db4breakfast.blogspot.com/2013/03/…