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.
- Why was it necessary to run SHRINKFILE more than one time to shrink the LDF?
- With the LDF at 1MB, why do I still have so many VLFs showing up in DBCC LOGINFO?
- 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.
Best Answer
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: