Thursday, December 10, 2015

Script: finding the object_id from the page_id

 
I had a situation where there was a page split ( monitored through Extended Events) and the page_id was 46565645. I wanted to find the table so that I could work with the fill factor of the indexes.
 
First I turned the following trace ON
 
 
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
 
 
In the above result Metadata: ObjectId = 245575913    gave me the object_id
 
I was able to find the table name with the page splits once I had the object_id .
 

No comments:

Post a Comment