- Adam Thurgar
Corruption detection
Currently I am looking at a database that is getting corrupted regularly.
The table in question has over 1.3 billion rows with no clustered index (vendor application and we cannot add a clustered index).
Error message when the backup failed was:
detected an error on page (4:30501725)
Then I went to this table that I did not know existed until recently:
SELECT * FROM [msdb].[dbo].[suspect_pages]; GO
Form this we can get the dbid, filenum and pagenum to use in a DBCC page to help find the object on this page
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
DBCC TRACEON (3604); DBCC PAGE (5, 4, 30501725, 0); DBCC TRACEOFF (3604); GO
Once you have the object id from the metadata you can get the object name Use XXX; GO SELECT OBJECT_NAME (614345303); GO
Then run a checktable to get more information:
DBCC CHECKTABLE ([OBJECTNAME]) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY
I suppose that I could automate most of this, but I don't want to be having corrupted databases that often.