In my last 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 at here, we know we can use DBCC PAGE to determine this information. And, after having read my last 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.
