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.