Finding a table name from a page ID - Paul S. Randal (2022)

  • Paul Randal
  • September 25, 2014

(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 youcome to work in the morning and notice that some new rows have been entered into the msdb.dbo.suspect_pages table during the night. Usuallythe 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 @0x00000004FD8C7980bpage = 0x00000004A2D14000 bhash = 0x0000000000000000 bpageno = (1:295)bdbid = 6 breferences = 0 bcputicks = 0bsampleCount = 0 bUse1 = 55116 bstat = 0x809blog = 0x15ab215a bnext = 0x0000000000000000 PAGE HEADER:Page @0x00000004A2D14000m_pageId = (1:295) m_headerVersion = 17 m_type = 17m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8200m_objId (AllocUnitId.idObj) = 84 m_indexId (AllocUnitId.idInd) = 256Metadata: AllocUnitId = 72057594043432960Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0Metadata: ObjectId = 245575913 m_prevPage = (0:0) m_nextPage = (0:0)pminlen = 8008 m_slotCnt = 1 m_freeCnt = 83m_freeData = 8107 m_reservedCnt = 0 m_lsn = (35:200:9)m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0m_tornBits = 1093512791 DB Frag ID = 1 Allocation StatusGAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATEDPFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL DIFF (1:6) = CHANGEDML (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 itinto 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 takingthe 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];GOSELECT 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.

  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.

    Reply

  2. Excellent post Paul !!

    Reply

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

    Reply

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

    Reply

    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.

      Reply

      1. Ah, if only. They’re vendor databases that I’m not allowed to poke at too much. Just get the pleasure of maintaining them.

        Reply

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

    Reply

    1. Boa ídéia. A visualização em certos casos fica bem mais organizada.

      Reply

  6. Great post Paul!
    I loved to read it

    Reply

    1. Great, Suddenly we got a watermelon from a seed.

      Reply

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

    Select * from sys.objects where object_id= 1426820145

    Reply

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

    Reply

    1. Look in the corruption category on my blog – several posts on metadata corruption.

      Reply

      1. Hi Paul, can I change the value of m_prevPage o m_nextPage using DBCC WRITEPAGE?

        Reply

        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

          Reply

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

    Reply

    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.

      Reply

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

    Reply

    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.

      Reply

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

    Reply

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

      Reply

      1. thank you @Paul Randal :)

        Reply

  12. Thank you for sharing this post. It is great post.

    Reply

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

    Reply

Leave a Reply

Other articles

The Curious Case of… disconnected AG secondary replica and transaction log out of space issue

(The Curious Case of…used to be part of our bi-weeklynewsletterbut we decided to make it aregular blog post instead so it can sometimes be more

Explore

The Curious Case of… eager writing and minimally-logged operations

(The Curious Case of…used to be part of our bi-weeklynewsletterbut we decided to make it aregular blog post instead so it can sometimes be more

Explore

The Curious Case of… random failing log shipping restores

(The Curious Case of…used to be part of our bi-weeklynewsletterbut we decided to make it aregular blog post instead so it can sometimes be more

Explore

The Curious Case of… data compression caching

(The Curious Case of…used to be part of our bi-weeklynewsletterbut we decided to make it aregular blog post instead so it can sometimes be more

Explore

The Curious Case of… tracking page compression success rates

(The Curious Case of…used to be part of our bi-weeklynewsletterbut we decided to make it aregular blog post instead so it can sometimes be more

Explore

Crazy, crazy end-of-year sale!

Update: We extended the sale through Christmas Day at midnight UTC! Our end-of-year sale is here and the discounts are insane! For example: Our signature

Explore

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.

Collaborate With Us

Choose a Course

Top Articles

You might also like

Latest Posts

Article information

Author: Merrill Bechtelar CPA

Last Updated: 09/29/2022

Views: 5766

Rating: 5 / 5 (70 voted)

Reviews: 85% of readers found this page helpful

Author information

Name: Merrill Bechtelar CPA

Birthday: 1996-05-19

Address: Apt. 114 873 White Lodge, Libbyfurt, CA 93006

Phone: +5983010455207

Job: Legacy Representative

Hobby: Blacksmithing, Urban exploration, Sudoku, Slacklining, Creative writing, Community, Letterboxing

Introduction: My name is Merrill Bechtelar CPA, I am a clean, agreeable, glorious, magnificent, witty, enchanting, comfortable person who loves writing and wants to share my knowledge and understanding with you.