Search for a damaged object by the number of the damaged page in MS SQL Server 2005

The other day, one of the MS SQL Server databases went to Suspect, there was an error message in the log:

Msg 7105, Level 22, State 9, Line 14
Database ID 6, page (1: 386499), slot 0 for LOB data type node does not exist. This is usually caused by transactions that can be read uncommitted data on a data page. Run DBCC CHECKTABLE.

The base was transferred to Emergency and attempted to perform DBCC CHECKDB, but the execution was immediately interrupted:

Msg 8921, Level 16, State 1, Line 13
Check terminated. Failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 7105, Level 22, State 9, Line 13
Database ID 6, page (1: 386499), slot 0 for LOB data type node does not exist. This is usually caused by transactions that can be read uncommitted data on a data page. Run DBCC CHECKTABLE.

With the same error, the execution of the DBCC CHECKALLOC command was interrupted. Everything was complicated by the fact that SQL Server was version 9.0.1399, i.e. RTM, without any updates.

Attempts to use the TABLOCK hint and explicitly increase transaction isolation did not lead to anything (there was enough space on disks with tempdb and DBCC CHECKALLOC with WITH ESTIMATEONLY ended with the same error). It was extremely undesirable to roll SP on a server with a damaged database, and it was absolutely incomprehensible with which specific object the problem was. In addition, it seemed that the DBCC CHECKDB message had little to do with reality, because there was one entry in msdb.dbo.suspect_pages, but the page number was different from the one that DBCC CHECKDB displayed.

In order to follow the DBCC CHECKDB instructions and execute DBCC CHECKTABLE, it was necessary to know the table. And after a long search, one instruction was found .

The first thing to do is to perform (in the context of a damaged database) DBCC PAGE (database_id, file_id, page_id, printopt):

DBCC TRACEON (3604); DBCC PAGE(5, 1, 3242342, 0) DBCC TRACEOFF (3604); 

or:

 DBCC PAGE(5, 1, 3242342, 0) WITH TABLERESULTS. 

If you are lucky (or you are playing on a live base), as a result you will see the Metadata: ObjectId field, and the object_id itself:



However, if you, like us, are unlucky, you will see the following:

Metadata: = Unavailable in offline DB

If the metadata is not available, all is not lost, in this case, we need the field m_objId (AllocUnitId.idObj). If m_objId = 255, trouble close the article and look for something else (try scripting everything you can and drag the data, perform DBCC CHECKDB with “recovery” parameters blindly, etc.).
The screenshot shows that I have m_objId = 9931, i.e. can continue.

Now you need to do some small calculations to calculate the Allocation Unit ID (for more information about Allocation Units, read here ):

Allocation Unit ID = m_objid * 65536 + (2 ^ 56)

In our case:

Allocation Unit ID = 9931 * 65536 + (2 ^ 56) = 72057594688765952

So, knowing the Allocation Unit ID, you can see what we have in the sys.allocation_units system view:

 SELECT * FROM sys.allocation_units WHERE allocation_unit_id = 72057594688765952 

And there, in case type = 1 or 3 (IN_ROW_DATA, ROW_OVERFLOW_DATA), the column container_id = sys.partitions.hobt_id (“Heap-Or-B-Tree ID”), i.e. You can run the query:

 SELECT * FROM sys.partitions WHERE hobt_id = 72057594661437440 

And here already there is a correct object_id and index_id. Now you can see what we have in sys.objects and sys.indexes, and just execute:

 SELECT OBJECT_NAME(object_id) 

Fortunately, in a real situation, and here, the nonclustered index turned out to be affected, after restructuring of which everything returned to normal (in fact, not, but this is another story).

References :
How to use DBCC PAGE
Troubleshooting and Fixing SQL Server Page Level Corruption
What Are Allocation Units?
Finding a table name from a page ID
sys.allocation_units