3

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)?

5

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.

| 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! – hansaplast 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 – hansaplast 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! – hansaplast Jul 7 '10 at 6:50
3

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.

| improve this answer | |
  • 1
    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 – hansaplast Jun 3 '10 at 15:23
  • Your disk is toast basically. – Remus Rusanu Jun 3 '10 at 18:13
  • 1
    for the record: the RAID controller was toast, the disks were fine, with the new RAID controller SQLIOSim runs through without errors – hansaplast Jul 7 '10 at 6:51
2

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?)

| 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 – hansaplast Jun 4 '10 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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