Saturday 25 February 2012

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

No comments:

Post a Comment