Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

This is what I saw in the windows error log:

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x19fedd20; actual: 0x19fed5e3). It occurred during a read of page (1:1764) in database ID 6 at offset 0x00000000dc8000 in file 'D:\mssql\local_repository_pbdiffimport.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe 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.

I ran

dbcc checkdb

which told me I should restore with option REPAIR_ALLOW_DATA_LOSS, so I eventually ran

DBCC CHECKDB (my_db_name, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

But that resulted in about 2'000 rows being lost. I restored a backup but now I'm afraid this will happen again since we already had a consistency problem in the same database about 2 weeks ago but then it happened in an index (recreated indexes solved the problem).

We have investigated the discs - RAID5 looks good, no errors, and also none of the disc-check-utilities have revealed any hardware problem.

Can this be caused by OS (Windows Server 2003) or by MSSQL (MSSQL Server 2005)?

share|improve this question
    
fyi: I've cross posted this question at microsoft.com and got a bunch of good remarks there as well: social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/… –  Philipp Keller Jun 3 '10 at 15:21

4 Answers 4

up vote 4 down vote accepted

The consistency could be caused by any of the factors hardware or software. Look at the SQL logs to investigate what potentially caused the issue.

My suggestions:

  • Ensure that the Database option, Page_Verify is set to CHECKSUM. This verifies all writes before they happen and is the default setting on SQL Server 2005.
  • Backup daily or multiple times a day(depending on need)
  • Setup maintenance plans to check your database for consistency on a daily basis
  • Keep your Windows Server and Sql Server updated with patches, 3rd Software too.
  • Read "Top tips for effective database maintenance" as it explains most of my suggestions in greater detail.

I recommend this article a lot because it was written to help SysAdmins who don't know to manage a database server.

share|improve this answer
    
Thanks a lot, Page_Verify is set to checksum, we have "Red Gate" that helps us with the backups. Good tip about the maintenance plan to check for consistency! –  Philipp Keller Jun 3 '10 at 7:11
    
Thanks for the link to "Top tips" - I read it and introduced a few things - daily check of consistency - alert if a consistency error occurrs –  Philipp Keller Jun 3 '10 at 15:22
    
for the record - after SQLIOSim threw so many errors we exchanged the RAID controller - SQLIOSim is now green, yey! –  Philipp Keller Jul 7 '10 at 6:50

You probably have hardware events reported in your system event log, you should investigate them.

Run SQLIOSIM to stress out the disk for +24 hours. If SQLIOSIM reports error, you'll have to contact your hardware vendor to investigate. It could be from disk, from the RAID array, from drivers. The OS and SQL are the least likely culprits.

See How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem.

share|improve this answer
    
thanks for the hint with SQLIOSim. In fact, SQLIOsim found a (repeating) error: Error: 0x80070467 Error Text: While accessing the hard disk, a disk operation failed even after retries. Description: Buffer validation failed on D:\sqliosim.mdx Page: 186061, offset 0x8 –  Philipp Keller Jun 3 '10 at 15:23
    
Your disk is toast basically. –  Remus Rusanu Jun 3 '10 at 18:13
    
for the record: the RAID controller was toast, the disks were fine, with the new RAID controller SQLIOSim runs through without errors –  Philipp Keller Jul 7 '10 at 6:51

Definitely not a SQL Server issue (well, very very very unlikely). ALSO not really likely to be an OS issue - simply because crap writes are way too obvious to survive long as an error.

This seriously points in the direction of hardware. RAM (you do use ECC?) is a possible culprit, as are any other kinds of related issues (RAID controller? Discs?)

share|improve this answer
    
I'm not sure we use ECC, how do I check that? We have a "write cache" activated in RAID that is 500MB, we now disabled this to check if this could be the problem –  Philipp Keller Jun 4 '10 at 8:31

Its an operating system level error which is caused due to damage Application programming Interface. SQL Server check every api during I/O operation if he finds check fails it reports an error.The main reason of this type of error is hardware issue. So must replace your hardware with healthy one.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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