Binaryworld - Finding SQL Server Object Name from PageNumber ... [ SQL Server -> Server Operations ]

Most of time when sql server throw an error will tell you object Id or PageNumber.

This article will show you how to find which object caused the error.

Step-By-Step Example

Lets say you got the folloing error from SQL Server

Error

Error: 7105, Severity: 22, State: 6
Page (1:212652), slot 1 for text, ntext, or image node does not exist.

This error doesn't tell you which object threw this error.

Enable output for DBCC PAGE command

Now very first thing you would do is run the following command in query analyzer

Click here to copy the following block

DBCC traceon(3604)

This will simply enable text output of DBCC PAGE command which will give us ObjectID

Run DBCC PAGE

Here is the sample command

Click here to copy the following block

--Error: 7105, Severity: 22, State: 6
--Page (1:212652), slot 1 for text, ntext, or image node does not exist.
dbcc page(nvcorp,1,212652)

This command will show you somethis like the following


PAGE: (1:212652)
----------------

BUFFER:
-------

BUF @0x01172E80
---------------
bpage = 0x634E4000        bhash = 0x00000000        bpageno = (1:212652)
bdbid = 9                 breferences = 0           bstat = 0x9
bspin = 0                 bnext = 0x00000000        

PAGE HEADER:
------------

Page @0x634E4000
----------------
m_pageId = (1:212652)     m_headerVersion = 1       m_type = 1
m_typeFlagBits = 0x0      m_level = 0               m_flagBits = 0x8000
m_objId = 1553596773      m_indexId = 0             m_prevPage = (0:0)
m_nextPage = (0:0)        pminlen = 39              m_slotCnt = 99
m_freeCnt = 369           m_freeData = 7625         m_reservedCnt = 0
m_lsn = (9176:714:123)    m_xactReserved = 0        m_xdesId = (0:0)
m_ghostRecCnt = 0         m_tornBits = 0    

..........
..........

< some more data which is truncated to make this simple >

..........
..........

Now if you observe the m_objId = 1553596773 in the output which is your object which produced the error.

Object name from ObjectId

Now we have object id so you can run the following command to get object name

Click here to copy the following block

select object_name(1553596773)

Full Script

Click here to copy the following block

DBCC traceon(3604)
go
--//Error: 7105, Severity: 22, State: 6
--//Page (1:212652), slot 1 for text, ntext, or image node does not exist.

dbcc page(nvcorp,1,212652)
go
--//Now look into the "dbcc page" output and find m_objId = <xxxxxxxxxx> where xxxxxxxxxx is your ObjectID. e.g in my case m_objId = 1553596773 so I run the following command

select object_name(1553596773)