A Few Changes in SQL Server 2012 (DBCC LOGINFO and DBCC CHECKDB)

Posted by on August 30, 2012 Leave a comment (9) Go to comments

Last week I put my first SQL Server 2012 instance into production, and therefore, started applying some of my Production scripts and jobs to that server. Upon doing so, I quickly noticed that two of my jobs were getting errors, two jobs that had been solid for years on other servers, so I took a look.

DBCC LOGINFO

First, on DBCC LOGINFO, the SQL team added a single field: RecoveryUnitId. I have tried to discover what this field is for, but have had no luck as of yet. Regardless, it means that my Check VLF Density script will have to change. This is a script that I run across all of my instances occasionally, but specifically whenever we have a DR site outage, to make sure that my Transaction Logs are not getting too full (due to their SQL Mirror Failover Partners being inaccessible). I have included the script below, it is not fancy, but it gets the job done. Note: It has a threshold set to only display databases whose VLF “density” is above 60%, this is configurable at the top of the script.

-- 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

DBCC CHECKDB

Secondly, on DBCC CHECKDB WITH TABLERESULTS, the SQL team added several columns and replaced a couple others. This required me to change a critical stored procedure that I use regularly to check those databases that, for a multitude of reasons, do not fit into my normal Backup –> Restore Elsewhere –> DBCC CHECKDB –> DELETE methodology (outlined here). This stored procedure is called by a SQL Agent Job and sends me e-mails with the results of its findings. I will also need to make this change to my other method, but I will be moving that process to a different server and will probably do that change at that time (since I cannot restore my 2012 databases to that 2008 R2 instance anyway).

Below, I have listed the stored procedure that I have on each database instance. Note: If you want to use this script, you would need to make sure you have DBMail Setup and Configured, and then supply the appropriate DBMail Profile and Recipient e-mail address(es) to the script via two variables declared at the top of the stored procedure. Furthermore, to narrow down the databases against which the procedure is ran, you can change the query that fills the @databasesToAnalyze table variable. Both of these sections are called out in comment lines at the beginning of the stored procedure.

Lastly, I would like to thank Robert Davis (Blog | Twitter) and Roji Thomas (Blog | Twitter) for helping me find a couple columns I left out, sometimes a second or third pair of eyes can really help. This is yet another reason why the SQL Twitter community (especially via the #sqlhelp hashtag) is so valuable.

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

I hope these scripts and information can be a help to someone.

Database Maintenance, T-SQL Problem-Solving
  1. Anandan September 11, 2012 at 7:17 AM

    Hello,
    Thanks for sharing the wonderful handy script to check the consistency of the databases in an instance.

    I am trying to save this script and getting the following errors. Please help me in correcting the errors.

    Msg 102, Level 15, State 1, Procedure usp_DBCCCheckDB_SpecialDatabases, Line 94
    Incorrect syntax near ‘;’.
    Msg 156, Level 15, State 1, Procedure usp_DBCCCheckDB_SpecialDatabases, Line 126
    Incorrect syntax near the keyword ‘ELSE’.
    Msg 102, Level 15, State 1, Procedure usp_DBCCCheckDB_SpecialDatabases, Line 168
    Incorrect syntax near ‘;’.
    Msg 156, Level 15, State 1, Procedure usp_DBCCCheckDB_SpecialDatabases, Line 200
    Incorrect syntax near the keyword ‘ELSE’.
    Msg 102, Level 15, State 1, Procedure usp_DBCCCheckDB_SpecialDatabases, Line 235
    Incorrect syntax near ‘;’.
    Msg 102, Level 15, State 1, Procedure usp_DBCCCheckDB_SpecialDatabases, Line 262
    Incorrect syntax near ‘;’.
    Msg 156, Level 15, State 1, Procedure usp_DBCCCheckDB_SpecialDatabases, Line 294
    Incorrect syntax near the keyword ‘ELSE’.

    Reply
    • SQLPhilosopher September 11, 2012 at 10:11 AM

      Anandan, thank you for bringing this to my attention. Somehow, when I pasted the script into the blog, WordPress ignored my code block and replaced the less-than and greater-than signs with their HTML equivalents. I have fixed the problem and the scripts should work for you now. Again, thank you for pointing this out and I am sorry for any confusion.

      Reply
      • Anandan September 14, 2012 at 4:55 AM

        Brad Hoff,
        Thanks for the attention now & Indeed this is a must have script for DBA.

        Now, I have successfully stored and executed the SP.

        Since I have stored the SP in a database namely DBA, I removed the schema name “administrator”.

        Now, the query successfully executed with the expected list.

        But Query completed with Errors. Here is the error message I got,

        DBCC execution completed. If DBCC printed error messages, contact your system administrator.
        Msg 2812, Level 16, State 62, Line 1
        Could not find stored procedure ‘master.usp_LastDBCCCheckDB_Set’.
        Mail queued.

        I guess something is missing here to be created.

        Kindly review and help, Thanks in advance.

        Reply
        • SQLPhilosopher September 17, 2012 at 12:29 PM

          Anandan, that was a part of the script that I use in my environment. I have commented it out for you. Thanks for letting me know and I hope the scripts help you out.

          Reply
  2. Richard December 13, 2012 at 10:24 AM

    Thanks for the CHECKINFO script, it has proved very useful in sorting out out logging performance issues.

    Reply
  3. PatrickG February 6, 2013 at 4:21 PM

    Useful information and nice scripts. Thanks for sharing them.

    Reply
  4. Matt July 27, 2015 at 10:14 AM

    Interesting read. Did you ever publish your script for your normal Backup –> Restore Elsewhere –> DBCC CHECKDB –> DELETE methodology process that you mentioned above?

    Reply
    • SQLPhilosopher November 10, 2015 at 3:35 PM

      Matt, I apologize for the delay in response. I have been out of commission for the past two years due to some health problems and I am just now getting back to writing and participating in the SQL community.

      To answer your question, Yes, I have this post that explains my methodology: https://www.sqlphilosopher.com/wp/2012/02/perform-dbcc-checkdb-weekly-using-red-gate-sql-virtual-restore/

      However, I do have one caveat to go along with that post. Red-Gate has since stopped supporting Hyperbac. I will work on writing a revised post that does not rely on Hyperbac. The basic methodology can still be used, but some of the huge benefit of Hyperbac would, of course, be lost.

      Reply
  5. Matt July 27, 2015 at 10:16 AM

    Interesting read. Did you ever publish your script for your normal normal Backup –> Restore Elsewhere –> DBCC CHECKDB –> DELETE methodology process that you mentioned above?

    Reply

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

CAPTCHA

*