• 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.


5 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t