This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous post, I shared demonstration on how to corrupt your table in SQL Server.
Generally, SQL DBAs are executing DBCC CHECKDB for checking and finding damaged table or data. But if your database size is very enormous and the result of DBCC CHECKDB is also huge, it is tough to find out suspected tables.
Then, what is the solution?
The solution is to check the suspect pages and find the table name using object id.
Check the below demonstration where I used one sample corrupted table, and more you can test the yesterday’s post for tbl_bad.
Check the suspect_pages:
|
1 |
SELECT * FROM [msdb].[dbo].[suspect_pages] |
Result:
|
1 2 3 |
database_id file_id page_id ----------- ----------- -------------------- 10 1 312 |
Execute below DBCC PAGE:
Database id =10
File no=1
Page id=312
Print option=0
|
1 2 3 4 |
DBCC TRACEON (3604); DBCC PAGE (10, 1, 312, 0); DBCC TRACEOFF (3604); GO |
Result:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
PAGE HEADER: Page @0x000000F815DC4000 m_pageId = (1:312) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8200 m_objId (AllocUnitId.idObj) = 138 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594046971904 Metadata: PartitionId = 72057594041335808 Metadata: IndexId = 1 Metadata: ObjectId = 565577053 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 8 m_slotCnt = 5 m_freeCnt = 7976 m_freeData = 206 m_reservedCnt = 0 m_lsn = (34:228:3) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -411585792 DB Frag ID = 1 Allocation Status GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED |
Find Metadata: ObjectId and get the object name:
|
1 |
SELECT OBJECT_NAME (565577053); |
Leave a Reply