{"id":4403,"date":"2014-09-25T01:42:30","date_gmt":"2014-09-25T08:42:30","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4403"},"modified":"2017-04-13T11:42:02","modified_gmt":"2017-04-13T18:42:02","slug":"finding-table-name-page-id","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/","title":{"rendered":"Finding a table name from a page ID"},"content":{"rendered":"

(Check out my Pluralsight online training course: SQL Server: Detecting and Correcting Database Corruption<\/a>.)<\/em><\/p>\n

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 :-)<\/p>\n

Imagine you\u00a0come to work in the morning and notice that some new rows have been entered into the msdb.dbo.suspect_pages<\/em> table during the night. Usually\u00a0the first thing you’re going to do is run DBCC CHECKDB<\/em>, 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.<\/p>\n

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<\/em>, seeing a lot of PAGELATCH_EX<\/a><\/em> waits and needing to figure out which table is involved from the page ID in the resource_description<\/em> column in the sys.dm_os_waiting_tasks<\/em> output.<\/p>\n

Going back to the first scenario, getting the data from the suspect_pages<\/em> table is easy:<\/p>\n

\r\nSELECT * FROM [msdb].[dbo].[suspect_pages];\r\nGO\r\n<\/pre>\n
\r\ndatabase_id file_id     page_id              event_type  error_count last_update_date\r\n----------- ----------- -------------------- ----------- ----------- -----------------------\r\n6           1           295                  2           2           2014-09-25 01:18:22.910\r\n<\/pre>\n
Finding the table name requires first using DBCC PAGE<\/em>. The syntax for DBCC PAGE<\/em> is:<\/p>\n
\r\ndbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])\r\n<\/pre>\n
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<\/em> – it’s perfectly safe. So taking the values from our suspect_pages<\/em> output, that gives us:<\/p>\n
\r\nDBCC TRACEON (3604);\r\nDBCC PAGE (6, 1, 295, 0);\r\nDBCC TRACEOFF (3604);\r\nGO\r\n<\/pre>\n
\r\nPAGE: (1:295)\r\n\r\nBUFFER:\r\n\r\nBUF @0x00000004FD8C7980\r\n\r\nbpage = 0x00000004A2D14000          bhash = 0x0000000000000000          bpageno = (1:295)\r\nbdbid = 6                           breferences = 0                     bcputicks = 0\r\nbsampleCount = 0                    bUse1 = 55116                       bstat = 0x809\r\nblog = 0x15ab215a                   bnext = 0x0000000000000000          \r\n\r\nPAGE HEADER:\r\n\r\nPage @0x00000004A2D14000\r\n\r\nm_pageId = (1:295)                  m_headerVersion = 17                m_type = 17\r\nm_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8200\r\nm_objId (AllocUnitId.idObj) = 84    m_indexId (AllocUnitId.idInd) = 256\r\nMetadata: AllocUnitId = 72057594043432960\r\nMetadata: PartitionId = 72057594039042048                                Metadata: IndexId = 0\r\nMetadata: ObjectId = 245575913      m_prevPage = (0:0)                  m_nextPage = (0:0)\r\npminlen = 8008                      m_slotCnt = 1                       m_freeCnt = 83\r\nm_freeData = 8107                   m_reservedCnt = 0                   m_lsn = (35:200:9)\r\nm_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0\r\nm_tornBits = 1093512791             DB Frag ID = 1                      \r\n\r\nAllocation Status\r\n\r\nGAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED\r\nPFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL                        DIFF (1:6) = CHANGED\r\nML (1:7) = NOT MIN_LOGGED\r\n<\/pre>\n
We’re interested in the output beginning Metadata:<\/em>. Those fields are not stored on the page itself. When I rewrote DBCC PAGE<\/em> for SQL Server 2005, I added the Metadata:<\/em> 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<\/em> and m_indexId<\/em> fields in SQL Server 7.0 and 2000).<\/p>\n
The Metadata: ObjectId<\/em> 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<\/em> to complete to know the extent of the damage.<\/p>\n
If you see the ObjectId<\/em> is 0, that means there was no metadata found. This could be because:<\/p>\n
    \n
  • The table that the page was part of has been deleted since the page corruption was logged<\/li>\n
  • The system catalogs are corrupt in some way<\/li>\n
  • The page is corrupt and so incorrect values were used to look up the metadata<\/li>\n<\/ul>\n
    In any case, you’ll need to wait for DBCC CHECKDB<\/em> to complete to know the extent of the damage.<\/p>\n
    If the ObjectId<\/em> is not 0 or 99, we can plug it\u00a0into the OBJECT_NAME<\/em> function to give us the name of the table:<\/p>\n
    \r\nSELECT OBJECT_NAME (245575913);\r\nGO\r\n<\/pre>\n
    \r\n--------------------------------------------------------------------------------------------------------------------------------\r\nNULL\r\n<\/pre>\n
    If you get the result above, then there are two possibilities:<\/p>\n
      \n
    1. You are in the wrong database context<\/li>\n
    2. The metadata for the database is corrupt, so wait for DBCC CHECKDB<\/em> to complete<\/li>\n<\/ol>\n
      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\u00a0the database_id<\/em> in the suspect_pages<\/em> output and plugging it into the DB_NAME<\/em> function.\u00a0Go into the correct database context and try again.<\/p>\n
      \r\nUSE [company];\r\nGO\r\nSELECT OBJECT_NAME (245575913);\r\nGO\r\n<\/pre>\n
      \r\n--------------------------------------------------------------------------------------------------------------------------------\r\nCustomerNames\r\n<\/pre>\n
      So there you go – hope this helps!<\/p>\n
      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<\/em> DMF – check it out. That will work as long as there aren’t any metadata or allocation bitmap corruptions.<\/p>\n
      PS2 Wayne Sheffield reminded me over email and in a comment that DBCC PAGE<\/em> doesn’t need 3604 if you use the WITH TABLERESULTS<\/em> option and programmatically crack the DBCC PAGE<\/em> results.<\/p>\n","protected":false},"excerpt":{"rendered":"
      (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\u00a0come to […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30,34,35,101],"tags":[],"class_list":["post-4403","post","type-post","status-publish","format-standard","hentry","category-corruption","category-dbcc","category-disaster-recovery","category-wait-stats"],"yoast_head":"\nFinding a table name from a page ID - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Finding a table name from a page ID - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(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\u00a0come to […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2014-09-25T08:42:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T18:42:02+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/\",\"name\":\"Finding a table name from a page ID - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2014-09-25T08:42:30+00:00\",\"dateModified\":\"2017-04-13T18:42:02+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Finding a table name from a page ID\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Finding a table name from a page ID - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/","og_locale":"en_US","og_type":"article","og_title":"Finding a table name from a page ID - Paul S. Randal","og_description":"(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\u00a0come to […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/","og_site_name":"Paul S. Randal","article_published_time":"2014-09-25T08:42:30+00:00","article_modified_time":"2017-04-13T18:42:02+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/","name":"Finding a table name from a page ID - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2014-09-25T08:42:30+00:00","dateModified":"2017-04-13T18:42:02+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/finding-table-name-page-id\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Finding a table name from a page ID"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4403","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4403"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4403\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4403"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4403"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4403"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}