SQL Swiss Army Knife #9 - Fixing VLFs

Blog do Ezequiel
Portuguese PFE SQL Server Team


Latest Updates
10/05/2014 - Latest update on the AdaptiveIndexDefrag procedure v1.5.8. Look for it in the "Ezequiel shortcuts" section on the right or just click HERE.

10/05/2014 - Updates to Maintenance plan deployment scripts available HERE

04/06/2014 - The SQL Swiss Army Knife Series Index

SQL Swiss Army Knife #9 - Fixing VLFs

Rate This
  • Comments 4

EDIT (09-08-2011): missing variable set for sql version. Thanks go to Calvin for finding this bug.

EDIT (26-03-2012): Updated script for SQL 2012 support.

EDIT (19-09-2012): Simplified logic.

EDIT (20-09-2012): Changed grow settings if not SQL Server 2012.

Hello all,
Here is another post on SQL scripts that may help DBAs, following the series "SQL Swiss Army Knife", this time revisiting the topic of VLFs. I blogged on this subject several times before and if you want to read more about it just click here.

Anyhow, a few months back I knew of a case where a database had over 1.2 million VLFs, and it took a very long time to recover when a restart was performed on the instance. More recently I as made aware of a database with over 930k VLFs. Thankfully, the database owner wanted to preemptively deal with the situation. The database owner was aware of the impact of a high VLF number and wanted a way of quickly finding and dealing with this kind of issue on other servers. This is why I wrote a script that gets an overview of the current VLF status in all databases of a given server, and if the number of VLFs are above a pre-determined threshold, also makes a suggestion of how many and how large the VLFs should be for that particular database.

The output will show:

  • The database name;
  • The transaction log current size and the size it will be after applying suggested changes. Both in MB;
  • The current number of VLFs and the number of VLFs that will remain after applying suggested changes;
  • The amount of growth iterations necessary to get to the suggested size;
  • The transaction log initial size and the autogrow size that should be set;

It will resemble this:

overview

Note that database and file names are purposely blacked out to preserve sensitive data.

In addition, a script is generated with the typical steps needed to deal with the issue, depending on whether the database is in Simple recovery model or not.

Something like this example:

script

Download script here: Fix_VLFs.sql

Hope you find it useful as much as I did.

Until next time!

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.

 
 
Social Media Sharing
|
Leave a Comment
  • Please add 1 and 3 and type the answer here:
  • Post
  • You declare these variables but never set them.

    DECLARE @majorver smallint, @minorver smallint, @build smallint

    This query can be used to set them.

    SELECT

     @majorver=PARSENAME(CONVERT(VARCHAR,SERVERPROPERTY('productversion')),4),

     @minorver=PARSENAME(CONVERT(VARCHAR,SERVERPROPERTY('productversion')),3),

     @build=PARSENAME(CONVERT(VARCHAR,SERVERPROPERTY('productversion')),2)

  • Thanks Calvin for finding this mistake. I meant to set them like this:

    SELECT

    @majorver = (@@microsoftversion / 0x1000000) & 0xff,

    @minorver = (@@microsoftversion / 0x10000) & 0xff,

    @build = @@microsoftversion & 0xffff

    I changed the download script accordingly.

  • Hey there Pedro - I like the script for sure but depending on version, I'd probably use a growth rate of 8000 instead of 8192 (because of the 4GB bug). In fact, you have the logic in there with major/minor version. You might just want to say if it's not 2008R2 or 2012 then 8000 instead of 8192. But, I can't remember the exact build of the fix in R2 (if it was in RTM or not). So, might be easier just to say 8000 except for 2012 and higher.

    Cheers,

    kt

  • Hi Kimberly, thanks for the input.

    You're right, of course, this was introduced in SQL 2005, and happens for growths multiples of 4GB and 8GB. I did some checking and this was fixed only in SQL 2012, and was not back-ported. So, I've rewritten the script accordingly.

    Cheers

    PL

Page 1 of 1 (4 items)