3

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.

8
  • What do the sql server error log and the system even lot say? Is the problem limited to one particular table and/or column? Are you running on a VM or physical hardware? Is this a server or a workstation, which might have a weaker hd controller? Do you experience other problems with the C:? Have you run a CHKDSK on the C:? May 29, 2012 at 22:10
  • Event Log shows similar info as the job history error: Read on "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\dbname.mdf" failed: 23(Data error (cyclic redundancy check).) and Unable to read and latch page (1:7699681) with latch type SH. 23(Data error (cyclic redundancy check).) failed. This is physical hardware, server machine Xeon processor, I'll run a chkdsk to see if there are any disk errors. This has happened to two different indexes (I've only had the error crop up twice). I'll also check sql server error log. May 29, 2012 at 22:21
  • CHKDSK found no errors Sql Server Error logger shows more of the same issues as the Event log. I do see these connection pool starvation issues too: The client was unable to reuse a session with SPID 87, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. and Error: 18056, Severity: 20, State: 29. May 29, 2012 at 23:39
  • 1
    I recommend using these index maintenance scripts instead of rolling your own. Also, when you do the reorg/rebuilds with no other load on the server, does the same thing happen?
    – Jon Seigel
    May 30, 2012 at 0:22
  • Thanks Jon, I was looking at those scripts yesterday trying to understand them, kinda heavyweight, but might be helpful to close the gaps where I might be doing something wrong. I'll take a look to see if I get the problem when the system isn't busy. May 30, 2012 at 16:05

2 Answers 2

3

The operating system returned error 23(Data error (cyclic redundancy check).) to SQL Server during a read

This indicates the operating system detected an issue reading data from the hard disk itself. When data is written onto the filesystem, the operating system calculates a CRC code for each block written; when that data is subsequently read back the operating system performs the CRC operation again on the read data and compares it with the CRC stored in the filesystem. If these two CRC codes don't match the OS reports error 23. I'd be very surprised if there isn't a hardware issue with either the drive itself, or perhaps some other component such as the motherboard or drive controller.

WITH (ONLINE=ON) in the Enterprise Edition of SQL Server will have no effect whatsoever on this issue. Online index operations vs offline index operations are no different at the operating system layer; data is read and written as necessary to either rebuild or recreate the index.

2
  • 1
    I never was able to get to the bottom of the problem, but I assume you are correct. Sep 27, 2013 at 22:58
  • Cheers, Matt Palmerlee
    – Hannah Vernon
    Sep 28, 2013 at 0:14
0

Well its good then restore from latest valid backup. Make sure you check consistency of backup using restore verifyonly before applying.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.