(Check out my Pluralsight online training course: SQL Server: Detecting and Correcting Database Corruption.)
This is a question that I was sent over email that I thought would make a good post, and I’m waiting to do a user group presentation in Australia at 2am, so this will keep me busy :-)
Imagine you come to work in the morning and notice that some new rows have been entered into the msdb.dbo.suspect_pages table during the night. Usually the first thing you’re going to do is run DBCC CHECKDB, but if your database is a few TB, that could be several hours before you know where the problem is, and which table may have lost data. You want to find out which table is involved as soon as possible so you can explore your disaster recovery options.
Another scenario is troubleshooting a poorly performing query workload, running my script to look at the currently waiting threads using sys.dm_os_waiting_tasks, seeing a lot of PAGELATCH_EX waits and needing to figure out which table is involved from the page ID in the resource_description column in the sys.dm_os_waiting_tasks output.
Going back to the first scenario, getting the data from the suspect_pages table is easy:
SELECT * FROM [msdb].[dbo].[suspect_pages]; GO
database_id file_id page_id event_type error_count last_update_date ----------- ----------- -------------------- ----------- ----------- ----------------------- 6 1 295 2 2 2014-09-25 01:18:22.910
Finding the table name requires first using DBCC PAGE. The syntax for DBCC PAGE is:
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
You can just use print option 0, as that just displays the page’s header. You also must enable trace flag 3604 to get any output from DBCC PAGE – it’s perfectly safe. So taking the values from our suspect_pages output, that gives us:
DBCC TRACEON (3604); DBCC PAGE (6, 1, 295, 0); DBCC TRACEOFF (3604); GO
PAGE: (1:295) BUFFER: BUF @0x00000004FD8C7980 bpage = 0x00000004A2D14000 bhash = 0x0000000000000000 bpageno = (1:295) bdbid = 6 breferences = 0 bcputicks = 0 bsampleCount = 0 bUse1 = 55116 bstat = 0x809 blog = 0x15ab215a bnext = 0x0000000000000000 PAGE HEADER: Page @0x00000004A2D14000 m_pageId = (1:295) m_headerVersion = 17 m_type = 17 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8200 m_objId (AllocUnitId.idObj) = 84 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594043432960 Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0 Metadata: ObjectId = 245575913 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 8008 m_slotCnt = 1 m_freeCnt = 83 m_freeData = 8107 m_reservedCnt = 0 m_lsn = (35:200:9) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 1093512791 DB Frag ID = 1 Allocation Status GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
We’re interested in the output beginning Metadata:. Those fields are not stored on the page itself. When I rewrote DBCC PAGE for SQL Server 2005, I added the Metadata: output to make it easier to find the object and index ID that the page is part of (as these used to be the m_objId and m_indexId fields in SQL Server 7.0 and 2000).
The Metadata: ObjectId field is what we want. If you see it is 99, then stop as that means the damaged page is part of the allocation system and not part of a table and you’ll need to wait for DBCC CHECKDB to complete to know the extent of the damage.
If you see the ObjectId is 0, that means there was no metadata found. This could be because:
- The table that the page was part of has been deleted since the page corruption was logged
- The system catalogs are corrupt in some way
- The page is corrupt and so incorrect values were used to look up the metadata
In any case, you’ll need to wait for DBCC CHECKDB to complete to know the extent of the damage.
If the ObjectId is not 0 or 99, we can plug it into the OBJECT_NAME function to give us the name of the table:
SELECT OBJECT_NAME (245575913); GO
-------------------------------------------------------------------------------------------------------------------------------- NULL
If you get the result above, then there are two possibilities:
- You are in the wrong database context
- The metadata for the database is corrupt, so wait for DBCC CHECKDB to complete
It’s most likely #1 that’s the problem, at least in my experience with helping people out. You can get the database name by taking the database_id in the suspect_pages output and plugging it into the DB_NAME function. Go into the correct database context and try again.
USE [company]; GO SELECT OBJECT_NAME (245575913); GO
-------------------------------------------------------------------------------------------------------------------------------- CustomerNames
So there you go – hope this helps!
PS1 Kenneth Fisher commented with some neat code that will do the job on 2012 and 2014 using the new sys.dm_db_database_page_allocations DMF – check it out. That will work as long as there aren’t any metadata or allocation bitmap corruptions.
PS2 Wayne Sheffield reminded me over email and in a comment that DBCC PAGE doesn’t need 3604 if you use the WITH TABLERESULTS option and programmatically crack the DBCC PAGE results.
33 thoughts on “Finding a table name from a page ID”
Great post… as always. What I know about corruption and how to fix it I know from Paul. Thanks Paul.
“You are in the wrong database context”
I must have done this 1000 times in the last 15 years.
Excellent post Paul !!
Great post as always. It’s always fun to see how the pages are put together.
I remembered seeing this post from Jason Strate on the new DMO SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS.
http://www.jasonstrate.com/2013/04/a-replacement-for-dbcc-ind-in-sql-server-2012/
Based on the information in it I wrote the following:
SELECT DB_NAME(susp.database_id) DatabaseName,
OBJECT_SCHEMA_NAME(ind.object_id, ind.database_id) ObjectSchemaName,
OBJECT_NAME(ind.object_id, ind.database_id) ObjectName, *
FROM msdb.dbo.suspect_pages susp
CROSS APPLY SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS(susp.database_id,null,null,null,null) ind
WHERE allocated_page_file_id = susp.file_id
AND allocated_page_page_id = susp.page_id
It seems like this should do the trick also but I don’t have a 2012+ instance with suspect pages and haven’t had a chance to make one. I’d love to know what you think.
Thanks
Kenneth
Several hours?! The 4+ terabyte databases I run DBCC checks on take at least a full 24 hours to run. Part of that is the SATA drives I had to settle for in order to get the necessary drive space to restore everything to, but geez.
Am I doing something wrong?
Are you using NOLOCK?
(Please, internet, don’t take the NOLOCK question seriously.)
There are all kinds of things you can do to make CHECKDB run faster – see the CHECKDB category for details. Also, multi-terabyte databases are usually in need of some partitioning to make them smaller, or at least allow filegroup-level backup/restore/CHECKFILEGROUP. I’d consider whether you can partition and/or archive data out to make your database smaller and more manageable.
Ah, if only. They’re vendor databases that I’m not allowed to poke at too much. Just get the pleasure of maintaining them.
One thing that I always see mentioned when talking about DBCC PAGE is that you need to use Trace Flag 3604 to see the results. However, there is a way to get the results displayed to your screen without using this trace flag: use “WITH TABLERESULTS” as an option to the DBCC PAGE statement. In Paul’s example above, this would be:
DBCC PAGE (6, 1, 295, 0) WITH TABLERESULTS;
When executing this, you get 4 columns back: ParentObject, Object, Field and Value. You can create a temporary table or table variable to match the output, create a string out of the DBCC PAGE statement, and use an INSERT INTO EXECUTE (string) to put the output from DBCC PAGE into the table. Then you could select out just the data that you are looking for.
Boa ídéia. A visualização em certos casos fica bem mais organizada.
Great post Paul!
I loved to read it
Great, Suddenly we got a watermelon from a seed.
Erik, sounds like you need this:
http://sqlperformance.com/2012/11/io-subsystem/minimize-impact-of-checkdb
You can make CHECKDB run a hell of a lot faster with a few tweaks. (For a 4TB db I would take a backup, restore and a dev box and run a full CHECKDB from there)
Rgds,
Scott
We can also use sys.objects to find more details about the object.
Select * from sys.objects where object_id= 1426820145
And how do you recommend to troubleshoot in case of the point you mentioned
2. The metadata for the database is corrupt, so wait for DBCC CHECKDB to complete.
Please post any recommended link too.
Regards
Look in the corruption category on my blog – several posts on metadata corruption.
Hi Paul, can I change the value of m_prevPage o m_nextPage using DBCC WRITEPAGE?
Not the topic of the post – but you can alter offsets as follows:
— prev page pointer
dbcc writepage (‘test’, 1, 75, 8, 6, 0x000000000000) — PageId_NULL
dbcc writepage (‘test’, 1, 75, 8, 6, 0x010000000100) — PFS page
dbcc writepage (‘test’, 1, 75, 8, 6, 0x555550000100) — out of range
— next page pointer
dbcc writepage (‘test’, 1, 75, 16, 6, 0x000000000000) — PageId_NULL
dbcc writepage (‘test’, 1, 75, 16, 6, 0x010000000100) — PFS page
dbcc writepage (‘test’, 1, 75, 16, 6, 0x555550000100) — out of range
Hello Paul,
Thank you for amazing article. As part of log-shipping when restoring a log i could below message and then database went into suspect mode. I have used your method to read the log backup but i don’t know how to co-relate with then info that i have. Can you please help in identifying the page id?Can i get page id from here log record ID (1797221:955720:167)?
“Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.During redoing of a logged operation in database ‘ABC’, an error occurred at log record ID (1797221:955720:167). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.”
The page referenced in the log record for that LSN is corrupt, and the page ID is listed in the output of fn_dump_dblog for that LSN. Or the log backup is corrupt in some way. Looks like you’ll need to reinitialize the log shipping secondary from a new full backup.
Hello Paul, thanks for the great article. Sorry to respond to a 2 year old post. I currently have several databases that all came from a common template database at some point in the past. DBCC CHECKDB reports no errors consistently, but when we are trying to implement TDE the process is failing on a suspect page that gets recorded in msdb..suspect_pages. I used DBCC PAGE to view the header information and the page ID in the header doesn’t match the page I’m passing in:
Command:
dbcc page (17,1,428321,0);
Result:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:2021305)
Also, the Metadata: ObjectID is showing 0, so it appears to not be part of any discernible object.
Outside of exporting/reimporting to a new database, might there be a way to wipe out this faulty page? I have tried shrinking, deleting ALL objects in a restored test db but that page still causes the TDE scan to fail.
Hmm – I’ve seen cases like this where there’s a page that’s mistakenly marked as allocated but no object owns it, in such a weird way that CHECKDB doesn’t catch it. The resolution is to shrink the database down below the page in question, or to move everything to a new database.
Thanks for another informative post but when i try to figure out the table name from DBCC PAGE command by passing other required parameters , it gives me the below error message
“DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 823, Level 24, State 2, Line 2
The operating system returned error 23(Data error (cyclic redundancy check).) to SQL Server during a read at offset 0x0000004aa66000 in file ‘E:\Index\Disk23\MSSQL.1\MSSQL\Data\XXXXXXX.ndf’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
”
Can help ?
That page is unreadable so you won’t be able to use DBCC PAGE to figure it out. Run a DBCC CHECKDB and it should tell you which table the corruption is in.
thank you @Paul Randal :)
Thank you for sharing this post. It is great post.
I’m a bit late to the party but have been using the info from this page for quite a while. Thought I’d finally say “Thank you for posting it”.
I also find it extremely aggravating that sys.dm_db_database_page_allocations will return how full pages are for out of row blobs but not for CI or NCI information. Still have to use DBCC PAGE for that.
Hey MR Randal
I have 1 row record from suspect_pages and When i use DBCC PAGE For specific page_id
my objectid = 0 and i cant find the table !
Would you help me
Thx by the way
Then the page has been deallocated maybe. You should run DBCC CHECKDB to see what’s wrong.
Hello Paul,
I’m dealing with two database corruption issues in my organization.
#Case-1
Msg 2576, Level 16, State 1, Line 15
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:49948) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594117226496 (type Unknown), but it was not detected in the scan. Msg 8904, Level 16, State 1, Line 13
Extent (1:1083344) in database ID 81 is allocated by more than one allocation object.
I’m not getting any info for the relevant objects. Or how can I go into the root of the corruption?
#Case-2
I’m unable to run DBCC against this database, it’s kicking me out with the below msg.
(As more details I found one table without an assigned allocation unit in the system table sys.allocation_units)
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Can you please guide ?
1) It’s from a dropped table that wasn’t cleaned up somehow – you can run repair to get rid of it.
2) Restore from backups
Dear Paul.
Please help or suggest
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 8939, Level 16, State 5, Line 5
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page (1:28841808). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Msg 8939, Level 16, State 6, Line 5
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page (1:28841808). Test ((m_type >= DATA_PAGE && m_type = PageHeaderOverhead () && m_freeData <= (UINT)PAGESIZE – m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.
You’ve got corruption and need to restore or repair.
old backups are not exisiting now and please let us know the repair options
Looks in Books Online for DBCC CHECKDB which has all the info you need. And start taking backups.