SQL Swiss Army Knife #10 - VLFs again. What’s your current status?

  • Article

EDIT (11-01-2011): A parenthesis was missing making the log_size_MB value skewed. Thanks Luis!

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

EDIT (19-11-2012): Added information from Fixing VLFs post, namely potential log size after fix, actual number of VLFs, amount of used VLFs, potential number of VLFs after fix, how many growth iterations to achieve that number, what the log initial size should be and a autogrow setting for each log.

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

This script will essentially lets you read useful VLF information in a more convenient way, and allows a quick “lay of the land” as far as VLFs go.
It will show you:

  • A list of all the databases with the count existing and used VLFs, the total and used log size, and finally the average VLF size within each database.

    image

  • With more detail, a list of all the recorded growth iterations that happened in each database and the VLF size per spawn, ordered by growth iteration (actually MinLSN). By recorded, I mean all the VLF spawns that still have a VLF present in the transaction log.

    image

  • A list of the number of VLFs within each database, grouped by VLF size and database.

    image

Hope you find it useful.

Download script here: view_VLFs.sql

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.