Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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:

share|improve this answer
    
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. –  Craig Efrein Aug 22 '13 at 12:51

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.)

share|improve this answer
    
Thank you for the explanation. Still would like to see what operations, if any are being stored in the LDF. –  Craig Efrein Aug 22 '13 at 13:25
    
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/… –  RLF Aug 22 '13 at 14:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.