Day 29: Fatal Errors 823, 824, 825, 832, OS error 23

1. Error 823: IO error OS cannot read the data
   

Error: 823, Severity: 24, State: 2.

The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset xxxx in file ‘xxx.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.

1. Operating system error 23(mostly from sql server 2000):
   

When you have external drive (USB) or the connected disk is corrupt and not responding continuously than you may get an operating system error 23.

Server: Msg 823, Level 24, State 1, Line 3

I/O error 23(Data error (cyclic redundancy check)) detected during read of BUF pointer =xxx , page ptr = xxxx, pageid = x), dbid = xxxxx, status = 0×801, file = f:\abc:.MDF.

BackupMedium::ReportIoError: write failure on backup device ‘E:\MSSQL\DATA\Backup\model\model_db.BAK’. Operating system error 23(Data error (cyclic redundancy check).).

BackupIoRequest::WaitForIoCompletion: read failure on backup device ‘f:\abc.mdf’. Operating system error 23(Data error (cyclic redundancy check).).

1. Error 824: IO error SQL server cannot read the data.
   

Msg 824, Level 24, State 2, Line 1.

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: xxxx; actual: xxxxx). It occurred during a read of page (1:x) in database ID xx at offset xxxxx in file ‘xxx.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.

1. Error 825: IO error, Sql server read the data but not with first attempt after trying couple of attempts (max 4) –introduced in sql server 2005.
   

Msg 825, Level 10, State 2, Line 1.

A read of the file ‘xxx.mdf’ at offset xxxxx succeeded after failing 4 time(s) with error: incorrect checksum (expected: xxx; actual: xxxxx). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

1. Error 832: Memory Error, Sql server read data in memory but due to memory problem data is lost/corrupt in memory.
   

A page that should have been constant has changed (expected checksum: xxx, actual checksum: xxxx, database x, file ‘xxxxxx.mdf’, page (1:x)). This usually indicates a memory failure or other hardware or OS corruption.

In this case

1. run BCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS to confirm what all errors
   
2. could also try :REPAIR_ALLOW_DATA_LOSS –Last option before restore(first take backup of the corrupted db)
   
3. check the disk –at Risk(at Disk management)
   
4. you may have to restore last good backup if you have,
   

All these error shows that your system is corrupt and Almost for all these error you may end up with restoring last good backup(sometime partial/page), so hope you should not get these errors.

http://sqlskills.com/BLOGS/PAUL/post/A-little-known-sign-of-impending-doom-error-825.aspx

http://sqlskills.com/BLOGS/PAUL/post/Example-20002005-corrupt-databases-and-some-more-info-on-backup-restore-page-checksums-and-IO-errors.aspx

http://sqlinthewild.co.za/index.php/2008/12/06/when-is-a-critical-io-error-not-a-critical-io-error/

http://sqlskills.com/BLOGS/PAUL/post/Dont-confuse-error-823-and-error-832.aspx

http://www.sqlservercentral.com/Forums/Topic519683-266-1.aspx#bm714760