Finding a table name from a page ID

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

  1. You are in the wrong database context
  2. 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.

25 thoughts on “Finding a table name from a page ID

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

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

  3. 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.)

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

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

  5. We can also use sys.objects to find more details about the object.

    Select * from sys.objects where object_id= 1426820145

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

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

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

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

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

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

  9. 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 ?

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.