In a prior blog post, I demonstrated how using DBCC PAGE can be automated by using the “WITH TABLERESULTS” option. In this post, we will continue with another look at how this can be done.

On a nice wintry day, your city ended up being covered in several feet of snow. During the course of the night, your own house had several power outages. Being concerned about your databases, you shoveled your way into your office, so that you could check on things. (Okay… actually you would just VPN in, but this is my story after all…)

Once you get into your server, you check the jobs and find that your job that runs DBCC CHECKDB has failed. Let’s assume that a power glitch has caused corruption in your database. In order to find out what all is affected, you run DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS. But, this happens to be on your 2TB database, and it will take a while for CHECKDB to finish so that you can find the scope of corruption.

You’d really like to know what tables are affected without having to wait. Luckily(?), this corruption was recorded in msdb.dbo.suspect_pages, and having just recently read Paul Randal’s post here, we know we can use DBCC PAGE to determine this information. And, after having read my prior blog post, you know that we can automate DBCC PAGE, so we can use our new friend “WITH TABLERESULTS” to find out what objects have been corrupted.

The suspect_pages table, documented here, has three particular columns of interest: database_id, file_id and page_id. These correspond nicely to the first three parameters needed for DBCC PAGE. To automate this, we need to know what information we need to return off of the page – and from Paul’s post, we know that this is the field “METADATA: ObjectId”. For this code example, let’s assume that this corruption is on page 11 of the master database (just change “master” to the name of your 2TB database).

IF OBJECT_ID('tempdb.dbo.#DBCCPAGE') IS NOT NULL DROP TABLE #DBCCPAGE;
CREATE TABLE #DBCCPAGE (
       ParentObject VARCHAR(255),
       [Object]     VARCHAR(255),
       Field       VARCHAR(255),
       [VALUE]     VARCHAR(255));
INSERT INTO #DBCCPAGE
EXECUTE ('DBCC PAGE (''master'', 1, 11, 3) WITH TABLERESULTS;');
 
SELECT schema_name = OBJECT_SCHEMA_NAME(ca.object_id),
       object_name = OBJECT_NAME(ca.object_id)
FROM   #DBCCPAGE
       CROSS APPLY (SELECT CONVERT(INTEGER, VALUE)) ca(object_id)
WHERE   Field = 'Metadata: ObjectId';

And there you go… you now know which object it is that has the corruption. In the same fashion, another interesting field that is returned is the IndexId – the Field value is “Metadata: IndexId”. It would be a similar exercise to grab that from this page also, an exercise that I’ll leave to you.

An automated method for getting the object from all suspect pages would entail encapsulating this logic into a cursor to spin through each row in the suspect_pages table (and I’ll even throw in getting the index_id also):

IF OBJECT_ID('tempdb.dbo.#DBCCPAGE') IS NOT NULL DROP TABLE #DBCCPAGE;
IF OBJECT_ID('tempdb.dbo.#SuspectObjects') IS NOT NULL DROP TABLE #SuspectObjects;
CREATE TABLE #DBCCPAGE (
        ParentObject    VARCHAR(255),
        [Object]        VARCHAR(255),
        Field           VARCHAR(255),
        [VALUE]         VARCHAR(255));
CREATE TABLE #SuspectObjects (
        database_id     INTEGER,
        file_id         INTEGER,
        page_id         INTEGER,
        object_id       INTEGER,
        index_id        INTEGER);
DECLARE @database_id    INTEGER,
        @file_id        INTEGER,
        @page_id        INTEGER,
        @SQLCMD         NVARCHAR(MAX);
DECLARE cCrackSuspectPages CURSOR LOCAL FAST_FORWARD FOR
SELECT  'EXECUTE (''DBCC PAGE (' +
            CONVERT(VARCHAR(15), database_id) + ', ' +
            CONVERT(VARCHAR(15), file_id) + ', ' +
            CONVERT(VARCHAR(15), page_id) + ') WITH TABLERESULTS;'');',
        database_id, file_id, page_id
FROM    msdb.dbo.suspect_pages;
OPEN cCrackSuspectPages;
FETCH NEXT FROM cCrackSuspectPages INTO @SQLCMD, @database_id, @file_id, @page_id;
WHILE @@FETCH_STATUS = 0
BEGIN
    TRUNCATE TABLE #DBCCPAGE;
    INSERT INTO #DBCCPAGE EXECUTE (@SQLCMD);
    
    INSERT INTO #SuspectObjects
            (database_id,
             file_id,
             page_id,
             object_id,
             index_id
            )
    SELECT  @database_id, @file_id, @page_id,
            (SELECT CONVERT(INTEGER, VALUE)
             FROM   #DBCCPAGE dp
             WHERE  dp.Field = 'Metadata: ObjectId'),
            (SELECT CONVERT(INTEGER, VALUE)
             FROM   #DBCCPAGE dp
             WHERE  dp.Field = 'Metadata: IndexId');
    FETCH NEXT FROM cCrackSuspectPages INTO @SQLCMD, @database_id, @file_id, @page_id;
END
CLOSE cCrackSuspectPages;
DEALLOCATE cCrackSuspectPages;
SELECT  database_name = DB_NAME(database_id),
        database_id,
        file_id,
        page_id,
        schema_name = OBJECT_SCHEMA_NAME(object_id, database_id),
        object_name = OBJECT_NAME(object_id, database_id),
        index_id
FROM    #SuspectObjects;

If you happen to be on SQL 2012 or higher, this can be greatly simplified by using the new (undocumented) DMO function sys.dm_db_database_page_allocations (and it also takes away the need to crack the page using DBCC PAGE).

SELECT  database_name = DB_NAME(sp.database_id),
        sp.database_id,
        sp.file_id,
        sp.page_id,
        schema_name = OBJECT_SCHEMA_NAME(dpa.object_id, sp.database_id),
        object_name = OBJECT_NAME(dpa.object_id, sp.database_id),
        dpa.index_id
FROM    msdb.dbo.suspect_pages sp
CROSS APPLY sys.dm_db_database_page_allocations(sp.database_id, NULL, NULL, NULL, 'LIMITED') dpa
WHERE   sp.file_id = dpa.allocated_page_file_id
AND     sp.page_id = dpa.allocated_page_page_id;

And there we go – yet another time when you might want to automate using DBCC PAGE. By now you should be able to see other uses for it – as long as you can get the database_id, file_id and page_id, you can automate the usage of it to retrieve the information that you are looking for.

Previous related posts:

Persisting DBCC Output
Automating DBCC Page

This post is re-published from my original post on SQL Solutions Group.

The post Automating DBCC PAGE, part 2 – determining object names on pages appeared first on Wayne Sheffield.