Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
I have looked at simple-talk.com/sql/database-administration/… - I don't really want to export to Excel, or anything like that. I'd like to have a SQL Agent job do the work! –  Max Vernon Oct 4 '12 at 6:33
    
Your current cursor has the same problems that sp_msforeachdb has - you need to declare it as LOCAL FAST_FORWARD to avoid potentially skipping databases. –  Aaron Bertrand Oct 4 '12 at 13:00
add comment

2 Answers

up vote 6 down vote accepted

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 + '
  INSERT #ti EXEC ' + QUOTENAME(name) 
    + '.sys.sp_executesql N''DBCC LOGINFO WITH NO_INFOMSGS'';
  INSERT #to SELECT ''' + 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;
share|improve this answer
    
Thanks Aaron! I can't believe how much cleaner that is. I was trying to determine how to build @sql from a SELECT statement - totally overlooking the obvious SELECT @sql = @sql + . Sometimes I hate my dev/procedural background! I always think it is better to use SET @sql = @sql + whereas SELECT @sql = type constructs make me shudder for some strange reason. Not so anymore!!! –  Max Vernon Oct 4 '12 at 13:44
2  
@MaxVernon I'm going to make it one line simpler. The USE command is not necessary. –  Aaron Bertrand Oct 4 '12 at 13:50
2  
And a caveat: this will break if you named a database with an apostrophe, e.g. O'Brien. You can fix that but it's ugly - easier to just not give databases poorly chosen names. :-) –  Aaron Bertrand Oct 4 '12 at 14:06
    
Nice - your statement is even backwards compatible! –  Max Vernon Oct 4 '12 at 15:45
add comment

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
share|improve this answer
2  
Be very careful about sp_msforeachdb - see here and here. –  Aaron Bertrand Oct 4 '12 at 13:04
    
Good point. I use your version where I work and was just doing a demo here but that's good to point out. –  cfradenburg Oct 4 '12 at 13:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.