In our test lab I've been experimenting with different jobs to keep our critical indexes from becoming too fragmented.
I'm currently using the approach described here: sys.dm_db_index_physical_stats (under the Examples -> D section: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes).
Basically every hour I query the
dm_db_index_physical_stats dynamic management view and if an index is between 5% and 30% fragmented I reorganize it, if it's greater than 30% fragmented I rebuild it. It seems to work fine during most of our testing, however, twice I've run into a problem where the scheduled job fails with an error:
The operating system returned error 23(Data error (cyclic redundancy check).) to SQL Server during a read at offset 0x00000eae3b2000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\database.mdf'.
Additional messages in the SQL Server error log and system event log may provide more detail.
This is a severe system-level error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. [SQLSTATE HY000] (Error 823)
When I run
DBCC CHECKDB a problem is reported in one of my indexes, the only way I'm able to fix this problem is by using
DBCC CHECKDB ('dbname', REPAIR_ALLOW_DATA_LOSS)
I'm not positive, but I suspect this error is caused by rebuilding or reorganizing indexes while my load tests are running in the test lab.
I've searched around and found nobody else reporting this consistency error related to rebuilding indexes. You can see more information about my problem on my blog post: SQL Server Index Corruption: CRC Error & DBCC CHECKDB
Is my approach to tuning indexes flawed? Should I not be trying to rebuild indexes on a "live" database (while traffic is hitting it)? Should I be using SQL Server Enterprise Edition's feature of rebuilding an index with (online=on)? Any help is appreciated.
I'm running SQL Server 2008 R2 Standard.