A few weeks back we did a blog post about a file grow bug that has been fixed with SQL Server Denali (Autogrow bug – fixed with Denali). The same blog posted explained why it is important not to have millions of VLF’s (Virtual Log Files). The method used for this is quite simple, and the syntax goes some thing like this:

dbcc loginfo('databasename');

Running this query will give you an output like this:

Then the question is, how can I do this on all the databases on my server?, I’m not doing running that command 100+ times. Here is the answer, we have created a script that loops through all your databases and collects this information and stores it into a temporary table. At the end we do a nice Group By and present you with the number of VLF per Logfile per database – here is the script:

CREATE TABLE tempdb.dbo.Loginfo_Temp
(
    FileID sql_variant null,
    Filesize sql_variant null,
    startoffset sql_variant null,
    FseqNO sql_variant null,
    Status sql_variant null,
    Parity sql_variant null,
    CreateLSN sql_variant null
)
CREATE TABLE tempdb.dbo.Loginfo
(
    DatabaseID int,
    FileID sql_variant null,
    Filesize sql_variant null,
    startoffset sql_variant null,
    FseqNO sql_variant null,
    Status sql_variant null,
    Parity sql_variant null,
    CreateLSN sql_variant null
)
DECLARE @sqlcmd varchar(4000)
DECLARE @Databaseid int
DECLARE loginfo CURSOR FOR
SELECT
 'use [' + Name + '];insert into tempdb.dbo.Loginfo_Temp (FileID, Filesize, startoffset, FseqNO, Status, Parity, CreateLSN) exec (''dbcc loginfo'')' as execcmd
, database_id
FROM sys.databases
WHERE (State = 0)
ORDER BY name
OPEN loginfo
FETCH NEXT FROM loginfo
INTO @sqlcmd, @Databaseid
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@sqlcmd)
INSERT INTO tempdb.dbo.Loginfo (DatabaseID, FileID, Filesize, startoffset, FseqNO, Status, Parity, CreateLSN)
SELECT @Databaseid, FileID, Filesize, startoffset, FseqNO, Status, Parity, CreateLSN
FROM tempdb.dbo.Loginfo_Temp
TRUNCATE TABLE tempdb.dbo.Loginfo_Temp
FETCH NEXT FROM loginfo
INTO @sqlcmd, @Databaseid
END
CLOSE loginfo
DEALLOCATE loginfo
SELECT
    DatabaseID,
    db_name(DatabaseID) as DbName,
    FileID,
    COUNT(*) as NumOfVLFS
FROM tempdb.dbo.Loginfo
GROUP BY DatabaseID, db_name(DatabaseID), FileID
ORDER BY DatabaseID
DROP TABLE tempdb.dbo.Loginfo
DROP TABLE tempdb.dbo.Loginfo_Temp

Not the best choise of datatypes that I have ever done – please don’t kill me for that one . Running the script gives you this result:

This makes life so much easier when you are monitoring the number of VLF’s in your databases. This script is easy to implement in your weekly monitorering procedure.

As a final note I’ll just mention, that in SQL Denali an extra column, RecoveryUnitID, is included in the output of “dbcc loginfo”, so the temp tables need to be updated accordingly to work with denali.