A Script A Day - Day 20 - User Database VLF Count

Today’s script will execute DBCC LOGINFO for all user databases.  This is a script I use to monitor the number of Virtual Log Files (VLF’s).  Kimberly Tripp has a great article on VLF’s here.

/*

      -----------------------------------------------------------------

      User Database VLF Count

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Set database context

USE master;

GO

-- Declare variables

DECLARE @DatabaseName VARCHAR(100)

DECLARE @MinDatabaseID INT

DECLARE @MaxDatabaseID INT

DECLARE @SQL VARCHAR(200)

           

-- Check for temporary table and drop it if it exists

IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL

    DROP TABLE [#Database] ;

-- Create temporary table

CREATE TABLE #Database

    (

      ID INT IDENTITY(1, 1),

      DatabaseName VARCHAR(100)

    )

       

-- Check for existing user databases

IF EXISTS ( SELECT  name

            FROM    sys.databases

            WHERE   database_id > 4

                    AND name NOT IN ( 'ReportServer', 'ReportServerTempDB',

                                      'distribution' ) )

    BEGIN

            -- Insert all database names into a temporary table

        INSERT  INTO #Database ( DatabaseName )

                SELECT  name

                FROM    sys.databases

                WHERE   database_id > 4

                        AND name NOT IN ( 'ReportServer', 'ReportServerTempDB',

                                          'distribution' )

                                                

            -- Set variables for the loop            

        SELECT  @MinDatabaseID = MIN(ID),

                @MaxDatabaseID = MAX(ID)

        FROM    #Database

            -- Begin loop

        WHILE @MinDatabaseID <= @MaxDatabaseID

            BEGIN

                        -- Get databaseName

                SELECT  @DatabaseName = DatabaseName

                FROM    #Database

                WHERE   ID = @MinDatabaseID

           

                        -- Build command

                SET @SQL = 'DBCC LOGINFO(' + '''' + @DatabaseName + ''''

                    + ');'

           

                        -- Try Catch block to execute SQL and handle errors              

                BEGIN TRY

                              -- Execute SQL

                    EXEC ( @SQL

                        )

                    PRINT 'DBCC LOGINFO RUN FOR ' + @DatabaseName

                END TRY

                BEGIN CATCH

                    SELECT  @DatabaseName,

                            message_id,

                            severity,

                            [text],

                            @SQL

                    FROM    sys.messages

                    WHERE   message_id = @@ERROR

                            AND language_id = 1033 -- British English

                END CATCH

           

                        --Get the next databasename ID

                SET @MinDatabaseID = @MinDatabaseID + 1    

                  -- End loop

            END

    END

GO

Enjoy!

Chris