SQL Server如何通过Page_ID找到对应的表
2019-09-29 09:56 潇湘隐者 阅读(1535) 评论(0) 编辑 收藏 举报其实本篇文章算是翻译Finding a table name from a page ID这篇文章，只是不想直接翻译。用自己的理解叙说出来。算是对上一篇博客"SQL Server如何找出一个表包含的页信息（Page)"的承前启后。
我们如果从日志或dump文件中发现页信息，那么能否通过页信息找到其关联的对象呢？ 答案是可以，而且非常简单。如下所示，这个DBCC PAGE的输出信息：
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:24188)
BUFFER:
BUF @0x000000018123EDC0
bpage = 0x000000012AAD6000 bhash = 0x0000000000000000 bpageno = (1:24188)
bdbid = 7 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 26077 bstat = 0x9
blog = 0x21cc7a7a bnext = 0x0000000000000000
PAGE HEADER:
Page @0x000000012AAD6000
m_pageId = (1:24188) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 439 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594066698240
Metadata: PartitionId = 72057594059030528 Metadata: IndexId = 0
Metadata: ObjectId = 631673298 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 193 m_freeCnt = 376
m_freeData = 7430 m_reservedCnt = 0 m_lsn = (47:211:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 1650828474 DB Frag ID = 1
Allocation Status
.....................................................................
输出信息里面有很重要的一部分信息（PAGE HEADER），如上图所示：
通过Metadata : ObjectId =631673298, 我们可以找出这个Page所涉及的对象为TestDeadLock（其实这个Page ID确实是从表TestDeadLock中找出的一个Page）
IndexId =0 就可以判断这个是数据页，不是索引页（Index Page）， m_type = 1就表示它是Data Page，m_level = 0 就表示页的层数为0
其实DBCC PAGE的输出信息里面可以挖掘到很多信息，就看你的贮备的知识多少和你是否对这些信息感兴趣。
Paul S. Randal的博客中提到，如果ObjectId为99，那么意味着这个页面已经损坏，你需要等待DBCC CHECKDB完成才能知道损坏的程度； 如果您看到ObjectId为0，则表示未找到对应的元数据。这可能是因为：
自记录页面损坏以来，已删除该页面所属的表
系统目录以某种方式损坏了
该页面已损坏，因此使用了不正确的值来查找元数据
无论如何，您都需要等待DBCC CHECKDB完成才能知道区损坏的程度。
另外，如果OBJECT_NAME (xxxx)返回NULL，要么是你执行脚本上下文的数据库弄错了，要么是元数据损坏了。需要DBCC CHECKDB返回详细信息。
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.
参考资料：
https://www.sqlskills.com/blogs/paul/finding-table-name-page-id/
