Check VLF Counts

Today I stumbled across a database with 87,302 VLF’s. Yes, that’s right… 87 THOUSAND. Most of our databases have a few dozen VLF’s, but this was an old database that had grown to 1.5 TB and had the default autogrowth settings left in tact. How did we discover this? During a routine reboot of the server, this database took 30 minutes to recover, but there were no error messages or status messages in the log.

Now, this blog post is not about VLF’s or why you should keep the number of VLF’s to a small, manageable number — although I hear under 50 is a good rule of thumb. No, the purpose of this blog post is to share a little script I wrote to check the number of VLF’s each database uses:

Create Table #stage(
    FileID      int
  , FileSize    bigint
  , StartOffset bigint
  , FSeqNo      bigint
  , [Status]    bigint
  , Parity      bigint
  , CreateLSN   numeric(38)
);
 
Create Table #results(
    Database_Name   sysname
  , VLF_count       int 
);
 
Exec sp_msforeachdb N'Use ?; 
            Insert Into #stage 
            Exec sp_executeSQL N''DBCC LogInfo(?)''; 
 
            Insert Into #results 
            Select DB_Name(), Count(*) 
            From #stage; 
 
            Truncate Table #stage;'
 
Select * 
From #results
Order By VLF_count Desc;
 
Drop Table #stage;
Drop Table #results;

This script is low-impact and is safe to run on large, production databases during business hours. However, just be aware that it’s using some undocumented commands.

For more information on VLF’s, check out these excellent articles:

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , , . Bookmark the permalink.

23 Responses to Check VLF Counts

  1. Karl F says:

    Thanks! Working with an ancient ‘inherited’ DB? Even after restoring to new DB server they bring old problems with them. This one “heat” had no checksums or torn page detection, lots of missing indices, and now it seems maybe a few too many VLFs? Going to have to do some reading. (at least I know the indices are defragmented, Michelle :) )

    Database_Name VLF_count
    heat 339
    dbaUtil 55
    tempdb 32
    msdb 22
    iDBA 4
    master 2
    model 2

  2. Hey Thanks for sharing.
    It’s neat to see details at twitter’s #sqlhelp turn into a blog post.

  3. Karl F says:

    Well – odd, starting with a 1MB log in 4 VLFs I tried adding 1024 MB to it and it jumped to 20 VLFs. That’s +16 VLFs. I even tried again, adding 1000 MB which is clearly less than 1 GB (according to Kimberly’s article > 1GB added should add 8 VLFs) – but even adding 1000 MB gave me +16 VLFs. *shrug* Needing to move on I realized I could just shoot for the 3072 ish I wanted to be at and would end up with 20 VLFs total. Probably not have to look back again on this one.

  4. Hey cool! I was actually going to blog about this soon, but you totally beat me to it. I have a similar script I created (which hopefully will show up below, I’m going to try pasting it in). Great minds think alike!

    Mine looks for a configurable number of VLFs per GB of log space, checks for high and low #s of VLFs (too few can also be a problem), and ignores logs 1GB, 16 VLFs are used
    SET @vlfPerGBHighLimit = 50;
    SET @vlfPerGBLowLimit = 2;

    DECLARE @loginfo table (
    fileid int
    , filesize bigint
    , startoffset bigint
    , fseqno bigint
    , status bigint
    , parity bigint
    , createLSN numeric (38,0)
    )

    DECLARE vlfCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT name
    from sys.databases
    where (database_id > 4 or name=’tempdb’)

    OPEN vlfCursor

    FETCH NEXT FROM vlfCursor INTO @dbName
    WHILE @@FETCH_STATUS = 0
    BEGIN

    print char(10) + ‘****Working on [' + @dbName + ']****’;

    –Get the log size

    select @dSQL=N’use [' + @dbName + N']; select @logSizeGBOUT = sum(size*8/1024./1024.) from sys.database_files where type_desc=”LOG”’
    exec sp_executesql @stmt = @dSQL, @ParmDefinition=N’@logSizeGBOUT numeric(15,1) OUTPUT’, @logSizeGBOUT = @logSizeGB OUTPUT;

    –PRINT @dSQL;
    PRINT ‘LogSizeGB=’+cast(@logSizeGB as nvarchar(12))

    –Ignore logs less than 1 GB
    IF @logSizeGB = @vlfPerGBHighLimit
    BEGIN
    select @msg= @dbName + ‘: ‘ + cast(@VLFCount as nvarchar(20))
    + ‘ VLFs is too many for a ‘ + cast(@logSizeGB as nvarchar(20))
    + ‘GB log. The log should be shrunk and grown in 8GB chunks.’

    RAISERROR (@msg, 16,1)
    END
    ELSE IF @VLFCount/@logSizeGB <= @vlfPerGBLowLimit
    BEGIN
    select @msg= @dbName + ': ' + cast(@VLFCount as nvarchar(20))
    + ' VLFs is too few for a ' + cast(@logSizeGB as nvarchar(20))
    + 'GB log. The log should be shrunk and grown in 8GB chunks.'

    RAISERROR (@msg, 16,1)
    END
    else print
    @dbName + ': ' + cast(@VLFCount as nvarchar(20)) + ' VLFs, which is managable.'

    END — Block checking VLFs on logs 1GB or larger

    –Move on…
    FETCH NEXT FROM vlfCursor INTO @dbName

    END
    CLOSE vlfCursor
    DEALLOCATE vlfCursor

  5. WIDBA says:

    We got burned by this – so part of our nightly server inventory is VLF counts, I then have a report that runs periodically and gives me any DB with high VLFs in it. (When you manage several hundred databases across so many servers, an inventory database with some data about the machines is a necessity.)

    This issue caused us to reset our auto growth as well to a higher number, and be diligent about file maintenance. The passive enhancement the report gives us is pointing out boxes that have had some change in their workload – which ends up being useful.

    Good Script!

  6. Roy says:

    I’ve a DB name with the reserved word. So, I made change on your script and it works perfect now. Thanks!

    CREATE TABLE #stage(
    FileID INT
    , FileSize BIGINT
    , StartOffset BIGINT
    , FSeqNo BIGINT
    , [Status] BIGINT
    , Parity BIGINT
    , CreateLSN NUMERIC(38)
    );

    CREATE TABLE #results(
    Database_Name sysname
    , VLF_count INT
    );

    EXEC sp_msforeachdb N’Use [?];
    Insert Into #stage
    Exec sp_executeSQL N”DBCC LogInfo([?])”;

    Insert Into #results
    Select DB_Name(), Count(*)
    From #stage;

    Truncate Table #stage;’

    SELECT *
    FROM #results
    ORDER BY VLF_count DESC;

    DROP TABLE #stage;
    DROP TABLE #results;

  7. Scott R. says:

    Michelle,

    Thanks for a great and useful script! Better to keep this awareness at the forefront than to let it lurk behind the scenes to cause unexpected behavior.

    I have two minor changes to the script:

    1. Change the Exec sp_msforeachdb … to Exec sp_MSforeachdb … – for those of us with case-sensitive DB collations (the mixed case sp_MSforeachdb is the exact spelling of this system SP and should work with all types of collations – hopefully).

    2. Add a third column Log_File_count to the #results temp table, and populate it in the second insert / select query with a Count(Distinct FileID). While most of us have single physical log files to support the multiple VLFs within a DB, other DBs have multiple physical files for the transaction log (intentionally to balance the load or to accommodate growth to another disk volume, or unintentionally!). This column will help identify such situations at the same time as the VLF summary is revealed.

    The fully modified script is presented below:

    CREATE TABLE #stage(
    FileID INT
    , FileSize BIGINT
    , StartOffset BIGINT
    , FSeqNo BIGINT
    , [Status] BIGINT
    , Parity BIGINT
    , CreateLSN NUMERIC(38)
    );

    CREATE TABLE #results(
    Database_Name sysname
    , VLF_count INT
    , Log_File_count INT
    );

    EXEC sp_MSforeachdb N’Use [?];
    Insert Into #stage
    Exec sp_executeSQL N”DBCC LogInfo(?)”;

    Insert Into #results
    Select DB_Name(), Count(*), Count(Distinct FileID)
    From #stage;

    Truncate Table #stage;’

    SELECT *
    FROM #results
    ORDER BY VLF_count DESC;

    DROP TABLE #stage;
    DROP TABLE #results;

    Again, many thanks for your script.

    Scott R.

  8. Peso says:

    You yongsters… 87k only? :-)
    Try 194k and see how THAT affects server restart.

  9. Peso says:

    You youngsters… 87k only? :-)
    Try 194k and see how THAT affects server restart.

    http://weblogs.sqlteam.com/peterl/archive/2009/12/09/Problem-with-SQL-Server-service-restart.aspx

  10. Susan Van Eyck says:

    Very helpful script! I’ve been exploring VLF counts in my databases one at a time. This is better :-)

    My tweak was adding NO_INFOMSGS to the DBCC command:

    Exec sp_executeSQL N”DBCC LogInfo(?) with no_infomsgs”;

  11. Trev says:

    Sounds like an interesting article if you’re already a SQL Server expert, but for the rest of us trying to learn, you never did say what a VLF is.

    I found someone else’s article that explains what they are. http://adventuresinsql.com/tag/vlf/

  12. Susan Van Eyck says:

    To get your wonderful script to work on a server where some of the SharePoint DBs have hyphens in their names, I needed to add square brackets around the ?s:
    `
    EXEC sp_msforeachdb N’Use [?];
    Insert Into #stage
    Exec sp_executeSQL N”DBCC LogInfo([?]) with no_infomsgs”;

  13. Thanks Michelle! Good script to have.

  14. Amit says:

    Ha …. I have seen a 50 GB database with 150,000 VLFs and it would take almost an Hr to recover in a clauster failover situation …. culprit -> Default model database caused log file to be set to autogrow @ 1 MB

  15. Oleg says:

    Hi Michelle,

    We have DB with 1,500,000 VLFs in it. I shrinked the log and pregrow it to 400GB. Week later it autogrow to 700GB in 8GB steps and we are in the same situation – 1,000,000 VLFs. It is SQL 2005. During the week we were loading abbout 300Gb data in it and we run readwrite filegroup backup for 5 days. I wonder what caused so many VLFs.
    Yes, it took 20+ hours to recover the DB after the boot.

    I appreciate it,

    Oleg

  16. Martin Lundblad says:

    Oleg,

    Did you know about the bug about growing a log file in multiples of 4 GB? See Paul Randal’s blog post: http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx.

    Your log file only grew by extents of 31 GB. Set the log file to increment by 8000 MB instead of 8192 MB.

  17. anonymous says:

    shit script !!!
    please post working scripts. I had to redo it all and then found it didn’t do what it was supposed to.

  18. Pingback: Changing your default settings for any new database is just compulsory « dbadiary

  19. David Benoit says:

    I updated the original using sp_foreachdb which is more reliable. You read about this procedure – http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

    Updated script that provides more information as follows;

    –From Michelle Ufford
    –http://sqlfool.com/2010/06/check-vlf-counts/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed:+SqlFool+(SQL+Fool)
    –Modified by David Benoit 05/2011

    DECLARE @SQLString NVARCHAR(MAX)
    , @DBName NVARCHAR(1000)
    , @DBID INT
    , @vlfPerGBHighLimit INT
    , @vlfPerGBLowLimit INT

    SELECT @vlfPerGBHighLimit = 50,
    @vlfPerGBLowLimit = 2;

    CREATE TABLE #stage(
    FileID INT
    , FileSize BIGINT
    , StartOffset BIGINT
    , FSeqNo BIGINT
    , [Status] BIGINT
    , Parity BIGINT
    , CreateLSN NUMERIC(38)
    );

    CREATE TABLE #results(
    Database_Name NVARCHAR(1000)
    , VLF_count INT
    , ActiveVLFs INT
    );

    CREATE TABLE #logspace(
    Database_Name NVARCHAR(1000)
    , LogSpace REAL
    , LogSpaceUsedPct REAL
    , Status INT
    );

    Exec sp_foreachdb N’Use ?;
    Insert Into #stage
    Exec sp_executeSQL N”DBCC LogInfo(?)”;

    Insert Into #results
    Select DB_Name(), Count(*), SUM(case when status != 0 then 1 else 0 end)
    From #stage;

    Truncate Table #stage;’

    INSERT INTO #logspace
    EXEC sp_executeSQL N’DBCC SQLPERF(LOGSPACE)’

    SELECT r.Database_Name,
    r.VLF_count,
    r.ActiveVLFs,
    l.LogSpace,
    l.LogSpace/r.VLF_count AS MBsPerVLF,
    r.VLF_count/(l.LogSpace/1024) AS VLFsPerGB
    FROM #results r INNER JOIN #logspace l
    ON r.Database_Name = l.Database_Name
    WHERE l.LogSpace >= 1000
    AND
    COALESCE(r.VLF_count/NULLIF((l.LogSpace/1024), 0), 0) >= @vlfPerGBHighLimit
    ORDER BY VLFsPerGB DESC;

    DROP TABLE #stage;
    DROP TABLE #results;
    DROP TABLE #logspace;

  20. Liam Gavin says:

    I have added log autogrowth settings to your query, works wonders through CMS….

    Create Table #stage(
    FileID int
    , FileSize bigint
    , StartOffset bigint
    , FSeqNo bigint
    , [Status] bigint
    , Parity bigint
    , CreateLSN numeric(38)
    );

    Create Table #results(
    Database_Name sysname
    , VLF_count int
    , log_auto_growth_mb int
    );

    Exec sp_msforeachdb N’Use ?;
    Insert Into #stage
    Exec sp_executeSQL N”DBCC LogInfo(?)”;

    Insert Into #results
    Select DB_Name(), Count(*), convert(int, max(growth / 128))
    From #stage s, sys.database_files
    where type = 1;

    Truncate Table #stage;’

    Select *
    From #results
    where VLF_count > 400
    Order By VLF_count Desc;

    Drop Table #stage;
    Drop Table #results;

  21. Dale Reece says:

    I had to add public access to the master database for the ##MS_PolicyTsqlExecutionLogin## account to get this to work. The policy passed when I ran it using my account, but failed when run by the job because the ##MS_PolicyTsqlExecutionLogin## user could not see the databases.

  22. Dale Reece says:

    Upon further evaluation, that does not solve the problem, either. I has to give the ##MS_PolicyTsqlExecutionLogin## access to the individual databases. Then I received an error stating that ##MS_PolicyTsqlExecutionLogin## account does not have permission to run DBCC loginfo. It seems that this will have to be run manually, rather than as a scheduled job.

  23. SQL4GNT says:

    Updated for SQL Server 2012. I added the column RecoveryUnitId


    CREATE TABLE #stage (
    RecoveryUnitId INT
    ,FileID INT
    ,FileSize BIGINT
    ,StartOffset BIGINT
    ,FSeqNo BIGINT
    ,[Status] BIGINT
    ,Parity BIGINT
    ,CreateLSN NUMERIC(38)
    );

    CREATE TABLE #results (
    Database_Name SYSNAME
    ,VLF_count INT
    );

    EXEC sp_msforeachdb N'Use ?;
    Insert Into #stage
    Exec sp_executeSQL N''DBCC LogInfo(?)'';

    Insert Into #results
    Select DB_Name(), Count(*)
    From #stage;

    Truncate Table #stage;'

    SELECT *
    FROM #results
    ORDER BY VLF_count DESC;

    DROP TABLE #stage;

    DROP TABLE #results;

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>