How can I 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 @cmdGetVL

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;

StackExchange Database Administrators Q#25408, answer score: 10