• Adam Thurgar

Database corruption, statistical indexes and backward compatibility levels


This past week, we had to fix a corrupt database. SQL Server 2008R2 but the database in question was in a compatibility level of 9.0 (2008). Upon investigation the problem seemed to be with the pages associated with a statistical or hypothetical index (_WA_sys). There were thousands of these statistical indexes and I started wondering how many had just been there since it was a SQL Server 2008 database and could the backward compatibility of these statistical indexes be causing issues. DBCC's were failing and backups were failing, so this needed to get fixed, but I wanted to avoid the REPAIR_ALLOW_DATA_LOSS of a DBCC Checkdb.

So we turned off the database options to auto create stats and auto update stats.

We then dropped all statistical indexes.

https://blogs.msdn.microsoft.com/mvpawardprogram/2013/09/09/sql-server-auto-statistics-cleanup/

We then ran a DBCC Checkdb and the errors had gone.

Just to be sure we then went and dropped and recreated all indexes on the problem table. Ran another DBCC Checkdb, no errors.

We then enabled the database options to auto create stats and auto update stats.

Finally we got rid of the old maintenance plan and created backup and maintenance jobs using Ola Hallengren's solution.

https://ola.hallengren.com/

But I still wonder about those old 9.0 statistical indexes. I usually drop them as part of any upgrade. I want the optimizer to create the very latest and greatest statistical indexes. Also I don't like having databases in a backward compatibility level, this is a recipe for disaster sometime in the future.


2 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