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).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
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).
|
1 2 3 4 5 6 7 8 9 10 11 |
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
