Sql-server – How to find databases with a large number of virtual log files

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