-- Only shows databases with a VLF density greater than 60% (configurable via @minimumDensity)
 
DECLARE @minimumDensity INT
SET @minimumDensity = 60
 
DECLARE @databaseList TABLE
(
    [database]            VARCHAR(256),
    [executionOrder]    [int] IDENTITY(1,1) NOT NULL
)
 
DECLARE @vlfDensity    TABLE
(
    [server]        VARCHAR(256),
    [database]        VARCHAR(256),
    [density]        DECIMAL(7,2),
    [unusedVLF]        INT,
    [usedVLF]        INT,
    [totalVLF]        INT
)
 
-- Starting in SQL Server 2012, an extra column was added to LogInfo, named RecoveryUnitId
-- Need to accomodate SQL Server 2012 (version 11.0)
DECLARE @versionString            VARCHAR(20),
        @serverVersion            DECIMAL(10,5),
        @sqlServer2012Version    DECIMAL(10,5)
 
SET        @versionString    = CAST(SERVERPROPERTY('productversion') AS VARCHAR(20))
SET        @serverVersion = CAST(LEFT(@versionString,CHARINDEX('.', @versionString)) AS DECIMAL(10,5))
SET        @sqlServer2012Version = 11.0 -- SQL Server 2012
 
IF(@serverVersion >= @sqlServer2012Version)
    BEGIN
        -- Use the new version of the table  
        DECLARE @logInfoResult2012 TABLE
            (
            [RecoveryUnitId]    INT NULL,
            [FileId]            INT NULL,
            [FileSize]            BIGINT NULL,
            [StartOffset]        BIGINT NULL,
            [FSeqNo]            INT NULL,
            [Status]            INT NULL,
            [Parity]            TINYINT NULL,
            [CreateLSN]            NUMERIC(25, 0) NULL
            )
    END  
ELSE  
    BEGIN
        -- Use the old version of the table
        DECLARE @logInfoResult2008 TABLE
            (
            [FileId]            INT NULL,
            [FileSize]            BIGINT NULL,
            [StartOffset]        BIGINT NULL,
            [FSeqNo]            INT NULL,
            [Status]            INT NULL,
            [Parity]            TINYINT NULL,
            [CreateLSN]            NUMERIC(25, 0) NULL
            )
 
    END
 
DECLARE
    @currentDatabaseID    INT,
    @maxDatabaseID        INT,
    @dbName                VARCHAR(256),
    @density            DECIMAL(7,2),
    @unusedVLF            INT,
    @usedVLF            INT,
    @totalVLF            INT
 
INSERT INTO
    @databaseList
    (
    [database]
    )
SELECT
    [name]
FROM
    [sys].[sysdatabases]
 
SELECT
    @currentDatabaseID = MIN([executionOrder]),
    @maxDatabaseID = MAX([executionOrder])
FROM
    @databaseList
 
WHILE @currentDatabaseID <= @maxDatabaseID BEGIN SELECT @dbName = [database] FROM @databaseList WHERE [executionOrder] = @currentDatabaseID IF(@serverVersion >= @sqlServer2012Version)
            BEGIN
                -- Use the new version of the table 
                DELETE
                    @logInfoResult2012
                FROM
                    @logInfoResult2012
 
                INSERT INTO
                    @logInfoResult2012
                EXEC('DBCC LOGINFO([' + @dbName + '])')
 
                SELECT
                    @unusedVLF = COUNT(*)
                FROM
                    @logInfoResult2012
                WHERE
                    [Status] = 0
 
                SELECT
                    @usedVLF = COUNT(*)
                FROM
                    @logInfoResult2012
                WHERE
                    [Status] = 2
 
                SELECT
                    @totalVLF = COUNT(*)
                FROM
                    @logInfoResult2012
            END          
        ELSE
            BEGIN
                -- Use the old version of the table 
                DELETE
                    @logInfoResult2008
                FROM
                    @logInfoResult2008
 
                INSERT INTO
                    @logInfoResult2008
                EXEC('DBCC LOGINFO([' + @dbName + '])')
 
                SELECT
                    @unusedVLF = COUNT(*)
                FROM
                    @logInfoResult2008
                WHERE
                    [Status] = 0
 
                SELECT
                    @usedVLF = COUNT(*)
                FROM
                    @logInfoResult2008
                WHERE
                    [Status] = 2
 
                SELECT
                    @totalVLF = COUNT(*)
                FROM
                    @logInfoResult2008
            END      
 
        SELECT
            @density = CONVERT(DECIMAL(7,2),@usedVLF) / CONVERT(DECIMAL(7,2),@totalVLF) * 100
 
        INSERT INTO
            @vlfDensity
            (
            [server],
            [database],
            [density],
            [unusedVLF],
            [usedVLF],
            [totalVLF]
            )
        VALUES
            (
            @@SERVERNAME,
            @dbName,
            @density,
            @unusedVLF,
            @usedVLF,
            @totalVLF
            )
 
        SET @currentDatabaseID = @currentDatabaseID + 1
    END
 
SELECT
    [server],
    [database],
    [density],
    [unusedVLF],
    [usedVLF],
    [totalVLF]
FROM
    @vlfDensity
WHERE
    [density] > @minimumDensity
ORDER BY
    [density] DESC

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC [administrator].[usp_DBCCCheckDB_SpecialDatabases]
-- =============================================
-- Author:        Brad Hoff
-- Create date: 2010_05_23
-- Change Log:    2012_08_29 - Brad Hoff - Changing sproc to accomodate changes in SQL Server 2012 DBCC CHECKDB command
--
-- Description:    Performs DBCC CheckDB against a subset of the databases
--                in the current instance.  This is to cover the databases
--                which cannot be checked via the normal weekly offload
--                process
-- =============================================
ALTER PROCEDURE [administrator].[usp_DBCCCheckDB_SpecialDatabases]
 
AS
BEGIN
    SET NOCOUNT ON;
 
    DECLARE    @currentDatabaseIdentifier    INT,
            @maxDatabaseIdentifier        INT,
            @currentDatabaseName        VARCHAR(256),
            @instanceName                VARCHAR(100),
            @statusMsg                    VARCHAR(MAX),
            @statusCode                    BIT,
            @msgSubject                    VARCHAR(200),
            @recipients                    NVARCHAR(MAX),
            @dbMailProfile                VARCHAR(64)
 
------------------------------------------------------------
 --=======================================================---
 --========================================================--
 ------------------------------------------------------------
    SET    @recipients = 'ENTER YOUR E-MAIL ADDRESSES HERE, SEPERATED BY SEMICOLONS'
    SET    @dbMailProfile = 'ENTER YOUR DBMail PROFILE HERE, DBMAIL IS REQUIRED FOR THIS SPROC'
------------------------------------------------------------
 --=======================================================---
 --========================================================--
 ------------------------------------------------------------
    SET @instanceName = @@SERVERNAME
    SET    @statusMsg = 'DBCC CheckDB Process is starting for select special databases on ' + @instanceName + ' at ' + CONVERT(VARCHAR,GETDATE(),120) + '
 
'
    SET    @statusCode = 0
 
    -- Starting in SQL Server 2012, several columns changed for the DBCC Check DB With Table Results command
    -- Need to accomodate SQL Server 2012 (version 11.0)
    DECLARE @versionString            VARCHAR(20),
            @serverVersion            DECIMAL(10,5),
            @sqlServer2012Version    DECIMAL(10,5)
 
    SET        @versionString    = CAST(SERVERPROPERTY('productversion') AS VARCHAR(20))
    SET        @serverVersion = CAST(LEFT(@versionString,CHARINDEX('.', @versionString)) AS DECIMAL(10,5))
    SET        @sqlServer2012Version = 11.0 -- SQL Server 2012
 
    DECLARE    @databasesToAnalyze    TABLE
    (
    [identifier]    INT    IDENTITY(1,1)    NOT NULL,
    [databaseName]    VARCHAR(256)        NOT NULL
    )
 
------------------------------------------------------------
--=======================================================---
--========================================================--
------------------------------------------------------------
-- REPLACE THIS SECTION WITH WHATEVER LOGIC YOU NEED FOR YOUR ENVIRONMENT
-- BY DEFAULT, THIS SCRIPT JUST CHECKS ALL DATABASES OTHER THAN TEMPDB
 
    INSERT INTO
        @databasesToAnalyze
        (
        [databaseName]
        )
    SELECT
        [name]
    FROM
        [sys].[databases]
    WHERE
        [name] NOT IN ('tempdb')
 
 -- END DATABASE SELECTION AREA
 ------------------------------------------------------------
 --=======================================================---
 --========================================================--
 ------------------------------------------------------------
 
    IF NOT EXISTS (
                    SELECT
                        *
                    FROM
                        [sys].[tables]
                    WHERE
                        [name] = 'tbl_CheckDBResults'
                    )
        BEGIN
            IF(@serverVersion >= @sqlServer2012Version)
                BEGIN
                    CREATE TABLE [dbo].[tbl_CheckDBResults]
                    (
                    [ServerName] VARCHAR(100) NULL,
                    [DatabaseName] VARCHAR(256) NULL,
                    [Error] INT NULL,
                    [Level] INT NULL,
                    [State] INT NULL,
                    [MessageText] VARCHAR(7000) NULL,
                    [RepairLevel] INT NULL,
                    [Status] INT NULL,
                    [DbId] INT NULL,
                    [DbFragId] INT NULL,
                    [ObjectId] INT NULL,
                    [IndexId] INT NULL,
                    [PartitionId] INT NULL,
                    [AllocUnitId] INT NULL,
                    [RidDbld] INT NULL,
                    [RidPruId] INT NULL,
                    [File] INT NULL,
                    [Page] INT NULL,
                    [Slot] INT NULL,
                    [RefDBId] INT NULL,
                    [RefPruId] INT NULL,
                    [RefFile] INT NULL,
                    [RefPage] INT NULL,
                    [RefSlot] INT NULL,
                    [Allocation] INT NULL,
                    [insert_date] DATETIME NULL
                    ) ON [PRIMARY]    
                END
            ELSE
                BEGIN
                    CREATE TABLE [dbo].[tbl_CheckDBResults]
                    (
                    [ServerName] VARCHAR(100) NULL,
                    [DatabaseName] VARCHAR(256) NULL,
                    [Error] INT NULL,
                    [Level] INT NULL,
                    [State] INT NULL,
                    [MessageText] VARCHAR(7000) NULL,
                    [RepairLevel] INT NULL,
                    [Status] INT NULL,
                    [DbId] INT NULL,
                    [Id] INT NULL,
                    [IndId] INT NULL,
                    [PartitionId] INT NULL,
                    [AllocUnitId] INT NULL,
                    [File] INT NULL,
                    [Page] INT NULL,
                    [Slot] INT NULL,
                    [RefFile] INT NULL,
                    [RefPage] INT NULL,
                    [RefSlot] INT NULL,
                    [Allocation] INT NULL,
                    [insert_date] DATETIME NULL
                    ) ON [PRIMARY]    
                END    
        END
 
    -- Setup default for Insert_Date field
    IF NOT EXISTS (SELECT [name] FROM [sys].[default_constraints] WHERE [name] = 'DF_CheckDBResult_insert_date')
        ALTER TABLE [dbo].[tbl_CheckDBResults] ADD  CONSTRAINT [DF_CheckDBResult_insert_date]  DEFAULT (getdate()) FOR [insert_date]
 
    IF NOT EXISTS (
                    SELECT
                        *
                    FROM
                        [sys].[tables]
                    WHERE
                        [name] = 'tbl_CheckDBResultSummary'
                    )
        BEGIN
            IF(@serverVersion >= @sqlServer2012Version)
                BEGIN
                    CREATE TABLE [dbo].[tbl_CheckDBResultSummary]
                    (
                    [ServerName] VARCHAR(100) NULL,
                    [DatabaseName] VARCHAR(256) NULL,
                    [Error] INT NULL,
                    [Level] INT NULL,
                    [State] INT NULL,
                    [MessageText] VARCHAR(7000) NULL,
                    [RepairLevel] INT NULL,
                    [Status] INT NULL,
                    [DbId] INT NULL,
                    [DbFragId] INT NULL,
                    [ObjectId] INT NULL,
                    [IndexId] INT NULL,
                    [PartitionId] INT NULL,
                    [AllocUnitId] INT NULL,
                    [RidDbld] INT NULL,
                    [RidPruId] INT NULL,
                    [File] INT NULL,
                    [Page] INT NULL,
                    [Slot] INT NULL,
                    [RefDBId] INT NULL,
                    [RefPruId] INT NULL,
                    [RefFile] INT NULL,
                    [RefPage] INT NULL,
                    [RefSlot] INT NULL,
                    [Allocation] INT NULL,
                    [insert_date] DATETIME NULL
                    ) ON [PRIMARY]
                END
            ELSE
                BEGIN
                    CREATE TABLE [dbo].[tbl_CheckDBResultSummary]
                    (
                    [ServerName] VARCHAR(100) NULL,
                    [DatabaseName] VARCHAR(256) NULL,
                    [Error] INT NULL,
                    [Level] INT NULL,
                    [State] INT NULL,
                    [MessageText] VARCHAR(7000) NULL,
                    [RepairLevel] INT NULL,
                    [Status] INT NULL,
                    [DbId] INT NULL,
                    [Id] INT NULL,
                    [IndId] INT NULL,
                    [PartitionId] INT NULL,
                    [AllocUnitId] INT NULL,
                    [File] INT NULL,
                    [Page] INT NULL,
                    [Slot] INT NULL,
                    [RefFile] INT NULL,
                    [RefPage] INT NULL,
                    [RefSlot] INT NULL,
                    [Allocation] INT NULL,
                    [insert_date] DATETIME NULL
                    ) ON [PRIMARY]
                END
        END
 
    SELECT
        @maxDatabaseIdentifier = MAX([identifier]),
        @currentDatabaseIdentifier = MIN ([identifier])
    FROM
        @databasesToAnalyze
 
    WHILE (@currentDatabaseIdentifier <= @maxDatabaseIdentifier) BEGIN SELECT @currentDatabaseName = [databaseName] FROM @databasesToAnalyze WHERE [identifier] = @currentDatabaseIdentifier DELETE [dbo].[tbl_CheckDBResults] FROM [dbo].[tbl_CheckDBResults] WHERE [ServerName] = @instanceName AND [DatabaseName] = @currentDatabaseName DELETE [dbo].[tbl_CheckDBResultSummary] FROM [dbo].[tbl_CheckDBResultSummary] WHERE [ServerName] = @instanceName AND [DatabaseName] = @currentDatabaseName -- Start DBCC Check DB IF(@serverVersion >= @sqlServer2012Version)
                BEGIN
                    EXEC('
                    INSERT INTO
                        [dbo].[tbl_CheckDBResults]
                        (
                        [Error],
                        [Level],
                        [State],
                        [MessageText],
                        [RepairLevel],
                        [Status],
                        [DbId],
                        [DbFragId],
                        [ObjectId],
                        [IndexId],
                        [PartitionId],
                        [AllocUnitId],
                        [RidDbld],
                        [RidPruId],
                        [File],
                        [Page],
                        [Slot],
                        [RefDBId],
                        [RefPruId],
                        [RefFile],
                        [RefPage],
                        [RefSlot],
                        [Allocation]
                        )
                    EXEC(''DBCC CHECKDB(''''' + @currentDatabaseName + ''''') WITH TABLERESULTS'')')
                END
            ELSE
                BEGIN
                    EXEC('
                    INSERT INTO
                        [dbo].[tbl_CheckDBResults]
                        (
                        [Error],
                        [Level],
                        [State],
                        [MessageText],
                        [RepairLevel],
                        [Status],
                        [DbId],
                        [Id],
                        [IndId],
                        [PartitionId],
                        [AllocUnitId],
                        [File],
                        [Page],
                        [Slot],
                        [RefFile],
                        [RefPage],
                        [RefSlot],
                        [Allocation]
                        )
                    EXEC(''DBCC CHECKDB(''''' + @currentDatabaseName + ''''') WITH TABLERESULTS'')')
                END
 
            UPDATE
                [dbo].[tbl_CheckDBResults]
            SET
                [ServerName] = @instanceName,
                [DatabaseName] = @currentDatabaseName
            WHERE
                [ServerName] IS NULL
 
            INSERT INTO
                [dbo].[tbl_CheckDBResultSummary]
            SELECT
                *
            FROM
                [dbo].[tbl_CheckDBResults]
            WHERE
                    [ServerName] = @instanceName
                AND    [DatabaseName] = @currentDatabaseName
                AND [MessageText] LIKE 'CHECKDB%'
 
            -- Start Analysis
            IF (@currentDatabaseName = 'master')
                BEGIN
                    IF EXISTS (
                                SELECT
                                    *
                                FROM
                                    [dbo].[tbl_CheckDBResultSummary]
                                WHERE
                                        [ServerName] = @instanceName
                                    AND    [DatabaseName] = @currentDatabaseName
                                    AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database ''master''%'
 
                                )
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Passed.
'
                            -- if successful, we need to update the extended property at the actual database
                            EXEC('EXEC [master].[administrator].[usp_LastDBCCCheckDB_Set] [' + @currentDatabaseName + ']')
                        END    -- Condition: A passing entry for this DB in DBCC Summary
                    ELSE IF EXISTS (
                                    SELECT
                                        [ServerName]
                                    FROM
                                        [dbo].[tbl_CheckDBResultSummary]
                                    WHERE
                                            [ServerName] = @instanceName
                                        AND    [DatabaseName] = @currentDatabaseName
                                    )
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Failed! (Check the tbl_CheckDBResults table)
'
                            SET @statusCode = 1
                        END    -- Condition: No passing entry for this DB in DBCC Summary
                    ELSE
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' was not properly performed (Check Configuration)
'
                            SET @statusCode = 1
                        END    -- Condition: No entry whatsoever for this DB in DBCC Summary        
 
                    IF EXISTS (
                                SELECT
                                    *
                                FROM
                                    [dbo].[tbl_CheckDBResultSummary]
                                WHERE
                                        [ServerName] = @instanceName
                                    AND    [DatabaseName] = @currentDatabaseName
                                    AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database ''mssqlsystemresource''%'
 
                                )
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + 'mssqlsystemresource' + ' Passed.
'
                            -- if successful, we need to update the extended property at the actual database
                            EXEC('EXEC [master].[administrator].[usp_LastDBCCCheckDB_Set] [' + @currentDatabaseName + ']')
                        END    -- Condition: A passing entry for this DB in DBCC Summary
                    ELSE IF EXISTS (
                                    SELECT
                                        [ServerName]
                                    FROM
                                        [dbo].[tbl_CheckDBResultSummary]
                                    WHERE
                                            [ServerName] = @instanceName
                                        AND    [DatabaseName] = @currentDatabaseName
                                    )
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + 'mssqlsystemresource' + ' Failed! (Check the tbl_CheckDBResults table)
'
                            SET @statusCode = 1
                        END    -- Condition: No passing entry for this DB in DBCC Summary
                    ELSE
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + 'mssqlsystemresource' + ' was not properly performed (Check Configuration)
'
                            SET @statusCode = 1
                        END    -- Condition: No entry whatsoever for this DB in DBCC Summary                            
                END
            ELSE
                BEGIN
                    IF EXISTS (
                                SELECT
                                    *
                                FROM
                                    [dbo].[tbl_CheckDBResultSummary]
                                WHERE
                                        [ServerName] = @instanceName
                                    AND    [DatabaseName] = @currentDatabaseName
                                    AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database %'
                                )
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Passed.
'
                            -- if successful, we need to update the extended property at the actual database
                            -- I am removing this for use by outside DBAs
                            -- EXEC('EXEC [master].[administrator].[usp_LastDBCCCheckDB_Set] [' + @currentDatabaseName + ']')
                        END    -- Condition: A passing entry for this DB in DBCC Summary
                    ELSE IF EXISTS (
                                    SELECT
                                        [ServerName]
                                    FROM
                                        [dbo].[tbl_CheckDBResultSummary]
                                    WHERE
                                            [ServerName] = @instanceName
                                        AND    [DatabaseName] = @currentDatabaseName
                                    )
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Failed! (Check the tbl_CheckDBResults table)
'
                            SET @statusCode = 1
                        END    -- Condition: No passing entry for this DB in DBCC Summary
                    ELSE
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' was not properly performed (Check Configuration)
'
                            SET @statusCode = 1
                        END    -- Condition: No entry whatsoever for this DB in DBCC Summary
                END
 
            SET @currentDatabaseIdentifier = @currentDatabaseIdentifier + 1
        END
 
    SET    @statusMsg = @statusMsg + '
DBCC CheckDB Process is starting for select special databases on ' + @instanceName + ' at ' + CONVERT(VARCHAR,GETDATE(),120) + '
'
 
    IF @statusCode = 0
        BEGIN
            SET @msgSubject = 'SUCCESS - DBCC CheckDB for special databases on ' + @instanceName
        END    -- Condition: There were no errors or failures in the consistency checking of this instance
    ELSE
        BEGIN
            SET @msgSubject = 'FAILURE - DBCC CheckDB for special databases on ' + @instanceName
        END    -- Condition: At least one consistency check either failed or resulted in an error
 
    EXEC [msdb].[dbo].[sp_send_dbmail]
        @profile_name = @dbMailProfile, 
        @recipients = @recipients,
        @body = @statusMsg,
        @subject = @msgSubject,
        @body_format = 'HTML';
 
END