How can I find databases with a large number of virtual log files?

Question

I have several databases on a production server that are in the hundreds of gigabytes, and have many thousands of transactions running through them on a daily basis.

Almost all these databases are mirrored using SQL Server Mirroring.

Even though we have carefully planned the physical log file sizes to match expected log file activity; occasionally something goes wrong and logs need to grow beyond our predicted maximum. We have setup all log files to grow by 8192MB, however when the database is under pressure to grow the log file it sometimes will only grow the log in very small chunks, thereby creating in some cases hundreds of thousands of virtual log files (VLFs).

I came to understand the importance of keeping the number of VLFs low when one of our production databases unexpectedly went into recovery with over 200,000 VLFs. Recovery took 20+ hours; during which time a portion of our business could not operate.

I need a solution that can monitor the number of virtual log files for all databases present on a server, sending an alert email if any particular log file has more than a given number of VLFs.

I know DBCC LOGINFO; returns the list of VLFs, however, I don't want to manually run this.

I created the following SQL statement that creates a nice table listing the databases, along with the number of VLFs however, I do not know how I can put this into a SQL Agent job to email our team whenever any database has over "x" number of VLFs.

DECLARE @cmd_per_database_prefix nvarchar(max);
DECLARE @cmd_per_database nvarchar(max);
DECLARE @database_name nvarchar(255);
SET @cmd_per_database = '';
SET @cmd_per_database_prefix = 
'
    SET NOCOUNT ON;
    DECLARE @vlf_count_table TABLE (database_name nvarchar(255), vlf_count int);
    DECLARE @params nvarchar(max);
    DECLARE @db_name nvarchar(255);
    DECLARE @vlf_count int;
    SET @params = ''@db_name nvarchar(255) OUTPUT, @vlf_count int OUTPUT'';
    DECLARE @cmdGetVLFCount nvarchar(max);
    SET @cmdGetVLFCount = 
    ''
        DECLARE @tab TABLE 
        (
            FileId int
            , FileSize nvarchar(255)
            , StartOffset nvarchar(255)
            , FSeqNo nvarchar(255)
            , Status int
            , Parity int
            , CreateLSN nvarchar(255)
        );
        DECLARE @cmd nvarchar(max);
        SET @cmd = ''''DBCC LOGINFO;'''';
        INSERT INTO @tab
        EXEC sp_executesql @cmd;
        SET @db_name = db_name();
        SET @vlf_count = (select count(*) FROM @tab t);
    '';
';
DECLARE cur CURSOR FOR
SELECT NAME 
FROM sys.databases
WHERE database_id > 4 and state=0;
OPEN cur;
FETCH NEXT FROM cur INTO @database_name;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd_per_database = @cmd_per_database + 
    '
        EXEC ' + @database_name + '.sys.sp_executesql @cmdGetVLFCount, @params, @db_name OUTPUT, @vlf_count OUTPUT;
        INSERT INTO @vlf_count_table (database_name, vlf_count) VALUES (@db_name, @vlf_count);
    ';
    FETCH NEXT FROM cur INTO @database_name;
END
SET @cmd_per_database = @cmd_per_database_prefix + @cmd_per_database + char(13) + char(10) + 'select * from @vlf_count_table t;';
EXEC sp_executesql @cmd_per_database;
CLOSE cur;
DEALLOCATE cur;

Attempting to output this using INSERT...EXEC into a table for attaching to an email using sp_send_dbmail is proving futile.

SQL Server chokes with:

An INSERT EXEC statement cannot be nested.
Msg 8164, Level 16, State 1, Line 5

Answer 1

Here is a slightly simpler approach that avoids the cursor and nested exec:

SET NOCOUNT ON;

CREATE TABLE #to
(
  DBName SYSNAME,
  FileCount INT
);

DECLARE @v INT;
SELECT @v = CONVERT(INT, PARSENAME(CONVERT(VARCHAR(32), 
  SERVERPROPERTY('ProductVersion')), 4));

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'CREATE TABLE #ti
  (
    ' + CASE WHEN @v >= 11 THEN 'RecoveryUnitId INT,' ELSE '' END + '    
    FileId int
    , FileSize nvarchar(255)
    , StartOffset nvarchar(255)
    , FSeqNo nvarchar(255)
    , Status int
    , Parity int
    , CreateLSN nvarchar(255)
);';

SELECT @sql = @sql + N'
  INSERT #ti EXEC ' + QUOTENAME(name) 
    + '.sys.sp_executesql N''DBCC LOGINFO WITH NO_INFOMSGS'';
  INSERT #to(DBName,FileCount) SELECT N''' + name + ''', COUNT(*) FROM #ti;
  TRUNCATE TABLE #ti;'
FROM sys.databases
WHERE database_id > 4 AND [state] = 0;

EXEC sp_executesql @sql;

SELECT DBName, FileCount FROM #to -- WHERE FileCount > [some threshold];

DROP TABLE #to;

Answer 2

You can create a temp table and use it inside the dynamic SQL. I didn't go based off your code but this shows the concept:

create table #mainTab 
(
    DatabaseName varchar(max)
    ,FileId int
    , FileSize nvarchar(255)
    , StartOffset nvarchar(255)
    , FSeqNo nvarchar(255)
    , Status int
    , Parity int
    , CreateLSN nvarchar(255)
);

exec sp_msforeachdb '
    DECLARE @tab TABLE 
    (
        FileId int
        , FileSize nvarchar(255)
        , StartOffset nvarchar(255)
        , FSeqNo nvarchar(255)
        , Status int
        , Parity int
        , CreateLSN nvarchar(255)
    );
    use ?
    insert into @tab exec(''DBCC LOGINFO'')

    insert into #mainTab
    select db_name(), * from @tab'

select DatabaseName, COUNT(*) from #mainTab group by DatabaseName

Answer 3

For SQL Server 2016 SP2 and later, the following code significantly simplifies obtaining a list of databases suffering from a large number of Virtual Log Files. The code uses the system dynamic management function, sys.dm_db_log_info which contains a single row for each VLF in the specified database. The function is officially supported, and documentation is available from Microsoft here.

--SQL Server 2016 SP2 and later

DECLARE @cmd nvarchar(max) = N'';
DECLARE @params nvarchar(max) = N'@VLFCutoff int';
DECLARE @VLFCutoff int = 10; --adjust this to the maximum number of VLFs that is "OK"

SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
UNION ALL
' END + N'
SELECT DatabaseName = ''' + d.name + N''', VLFCount = COUNT(1)
FROM sys.dm_db_log_info(' + CONVERT(nvarchar(10), d.database_id) + N')
'
FROM sys.databases d
WHERE d.state_desc = N'ONLINE'
    AND d.user_access_desc = N'MULTI_USER'
ORDER BY d.name;

SET @cmd = N'SELECT *
FROM (
' + @cmd + N'
) src
WHERE src.VLFCount > @VLFCutoff;'

EXEC sys.sp_executesql @command = @cmd, @params = @params, @VLFCutoff = @VLFCutoff;

You should adjust the @VLFCutoff parameter to limit the list of databases shown. Probably a good starting point would be 10,000. I have it set to 10 for testing purposes which means it will report almost all databases.

The output on my test instance looks like:

╔═══════════════════╦══════════╗
║   DatabaseName    ║ VLFCount ║
╠═══════════════════╬══════════╣
║ LogGrowthTest     ║    19789 ║
║ msdb              ║       55 ║
║ mssqlr            ║       13 ║
║ performance_audit ║       12 ║
║ RestoreTest       ║       24 ║
║ StackOverflowCore ║       16 ║
║ StackOverflowCS   ║      107 ║
║ tempdb            ║       16 ║
║ TestDB            ║       49 ║
╚═══════════════════╩══════════╝