VLF Alert

Hi

Does anyone know a way to alert on a db that has a high VLF count?

  • http://www.sqlservercentral.com/scripts/Transaction+Log/138551/

    Enjoy, script and alert on the link...

    Edward Pochinski III

    www.sqlsharkconsulting.com

    www.blackriverflyshop.com

  • I am using this code since it uses sys.dm_db_log_info function introduced in SQL Server 2017 (backported to SQL Server 2016 SP2).

    First it checks, if the function is available on target instance. If yes the monitoring code is quite trivial. If its not available (older version of SQL Server) the well-known code kicks in.

    Using sys.dm_db_log_info has one great advantage: It does not connect to each database for querying the VLF details. If you are using Log Shipping, the old check logic will fail, if log shipping is restoring the database transaction log even though its state is checked (state = 0). It may hapen that the database is still inacccessible during transition to online state. This is why it's recommended to additionally check collation name in sys.database if it's not null. But even then we had false alarms during T-Log restores.
    They completely disappeared after upgrading SQL Server 2016 SP1 to SP1 and using the new function.

    --Monitor SQL vlf count above a threshold
    --Specify the number of files of which anything above this value, will trigger an alert
    DECLARE @COUNT_THRESHOLD INT = '50'
    SET NOCOUNT ON;
    -----------------------------------------------------------
     
    --If an alert is raised by SQLdm, then remove the remarks on the bottom portion of the script
    --and run this query again against the remote Monitored instance to display the databases
    --which are above the threshold
    -----------------------------------------------------------
     
    ----------------------------------------------------------
    IF EXISTS (SELECT name
               FROM sys.system_objects
               WHERE name = N'dm_db_log_info'
                 AND SCHEMA_NAME(schema_id) = N'sys')
    BEGIN
        SELECT COUNT(*)
        FROM (SELECT [name], COUNT(l.database_id) AS 'vlf_count'
              FROM sys.databases s
                   CROSS APPLY sys.dm_db_log_info(s.database_id) l
              WHERE name NOT IN (N'tempdb', N'master', N'msdb', N'model', N'reportservertempdb')
              GROUP BY [name]) vlfs
        WHERE vlf_count > @COUNT_THRESHOLD;
    END
    ELSE
    BEGIN
        DECLARE @query VARCHAR(100)
        DECLARE @dbname sysname
        DECLARE @vlfs INT
        DECLARE @databases TABLE (dbname sysname)
        INSERT INTO @databases
        SELECT name
        FROM sys.databases
        WHERE state = 0 --just check online databases
          AND collation_name IS NOT NULL --even if a database is marked as online, it may not  accept connections. According to Books online for sys.databases we have to check collation column, too.
          AND name NOT IN (N'tempdb', N'master', N'msdb', N'model', N'reportservertempdb');

        DECLARE @vlfcounts TABLE
               (dbname sysname,
               vlfcount INT)
        DECLARE @dbccloginfo TABLE
        (
               RecoveryUnitId INT,
               fileid TINYINT,
               file_size BIGINT,
               start_offset BIGINT,
               fseqno INT,
               [status] TINYINT,
               parity TINYINT,
               create_lsn NUMERIC(25,0)
        )
     
        WHILE EXISTS(SELECT TOP 1 dbname FROM @databases)
        BEGIN
     
               SET @dbname = (SELECT TOP 1 dbname FROM @databases)
               SET @query = 'dbcc loginfo (' + '''' + @dbname + ''') '
     
                --We have to check within the loop again, because in the meantime the status may have changed and the database is in restoring (e.g. through Log Shipping)
                IF EXISTS (SELECT name
                           FROM sys.databases
                           WHERE state = 0 --just check online databases
                            AND collation_name IS NOT NULL --even if a database is marked as online, it may not  accept connections. According to Books online for sys.databases we have to check collation column, too.
                            AND name = @dbname)
                BEGIN

                   INSERT INTO @dbccloginfo
                   EXEC (@query)
     
                   SET @vlfs = @@rowcount
     
                   INSERT @vlfcounts
                   VALUES(@dbname, @vlfs)

                END;
     
               DELETE FROM @databases WHERE dbname = @dbname
     
        END
     
        SELECT COUNT (*)
        FROM @vlfcounts
        WHERE vlfcount > @COUNT_THRESHOLD;
        --
        --select dbname, vlfcount
        --from @vlfcounts
        --order by dbname
    END;
    SET NOCOUNT ON;