How to get table name from database id, file id, page id in MS SQL 2008?

Ankush picture Ankush · May 11, 2011 · Viewed 16.8k times · Source

I've deadlock graph in which the locked resource is mentioned by these three fields DB ID, File ID, Page ID. There is also some associated objectid. All I want to know is what table this page belongs. I tried DBCC PAGE(dbid, fileid, pageid) with tableresults but that doesn't show any table name.

Any idea how to get this?

Update: Also tried SELECT name From sys.indexes WHERE object_id = 123 and Index_id = 456 Here 123 is m_objid (next ObjectId) and 456 is m_indexid (next IndexId) which I get as output for DBCC Page command. All I get is NULL.

Answer

Ed Harper picture Ed Harper · May 11, 2011

To get results from DBCC PAGE you must enable traceflag 3604, otherwise the results go to the SQL server log:

dbcc traceon (3604)

then try the command

dbcc page ( dbid, filenum, pagenum , 3)

The fourth parameter is printopt:

The printopt parameter has the following meanings:

0 - print just the page header
1 - page header plus per-row hex dumps and a dump of the page slot array 
    (unless it's a page that doesn't > have one, like allocation bitmaps)
2 - page header plus whole page hex dump
3 - page header plus detailed per-row interpretation

definition from here