VSUS (VLF Segments of Unusual Size)

Enjoying another little train trip around an airport.

and while waiting around for my flight I thought I would try and tackle fixing that 16 VLF segments of 16 GB each situation I described in my previous blog post.

You might just think "well that should be just like fixing the too many VLFs issue, just shrink it down and grow it back up". 

There is a bit of a hitch to this. That #1 VLF segment in the primary transaction log file is sticky.

You cannot get rid of that first 16 GB VLF without dropping the log entirely and I do not recommend that. Ever. Not if you love your data.

Here are the factors that make this first VLF segment "sticky";

1. SHRINKFILE will only shrink in VLF sized increments.
2. SHRINKFILE will  not shrink your transaction log to fewer than 2 VLF segments.
3. You cannot REMOVE the primary transaction log file (that is the transaction log file that was created during the original CREATE DATABASE statement)

Msg 5020, Level 16, State 1, Line 1

The primary data or log file cannot be removed from a database.

So, what can you do

Well, using the technique below you can get to a log file with the all but the first VLF down to the size you desire. Please note that I did not create a transation log of 256 GB to test this technique. I am not that patient.

Start by freeing up as much as you can by either running a transaction log backup (FULL) or CHECKPOINT (SIMPLE).

Run

DBCC LOGINFO(VLFTEST)

To see what you have to work with.  The number of rows indicates the number of VLF segments your log has.  Take note of where the lowest VLF of Status 2 is located.

When the first VLF segment is the only VLF with a status of 2 (like pictured below), shrink the primary transaction log file down to two VLFs

 [Just a note here.  Shrink can only release VLMs up to the "lowest" one. If your lowest #2 is down the list a ways it you will have to wait around for enough transactions to go through for the log for it to wrap back around to the front. Depending upon the workload of your system you may need to be patient (Yes, you could do something like create a temp table and INSERT and DELETE a bunch of data into it to get your transaction log to roll around, but this may introduce unwanted risk into a production system and waiting may be a better choice).]

DBCC SHRINKFILE (VLFTEST_LOG, 1)

You should be rewarded with 2 VLFs left

Now, remember that it will not let you have fewer than 2 VLFs so to trick it we will temporarily add a second transaction log file

ALTER DATABASE VLFTEST

    ADD LOG FILE (NAME=VLFTEST_LOG2,

                                  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VLFDATA_Log2.ldf',

                                  SIZE = 100 MB)

Shrink the primary transaction log file again and it will get down to 1 VLF segment in the first file

DBCC SHRINKFILE (VLFTEST_LOG, 1)

Using a series of ALTER DATABASE MODIFY FILE statements grow primary transaction log file out to the size and VLFcount you desire (like 480 512 MB VLF segments plus that first 16 GB segment for last week's example).

And finally, remove that second log file shrink that second transaction log file with EMPTYFILE option to move everything back into the primary file.

DBCC SHRINKFILE (VLFTEST_LOG2, EMPTYFILE)

Now remove the unwanted second transaction log file

ALTER DATABASE VLFTEST

                    REMOVE FILE VLFTEST_LOG2        

You should be left with your desired number of VLF segments in the primary transaction log file with only the first segment being a segment of unusual size.

If anyone knows of a method to "resize" that first VLF segment that does not involve dropping the database or log in some fashion please drop me a line.