2

A MS SQL Server - Database that worked fine for the last weeks suddenly threw the following error:

System.Data.SqlClient.SqlException: Warning: Fatal error 823 occurred at date / time
Note the error and time, and contact your system administrator.

What does this error mean, and what can i do about it?

I tried

DBCC CHECKDB('mydatabase')

but i only got more error messages.

Thanks in advance!

3 Answers 3

5

As Splattne said, 823 means there's an I/O subsystem problem. An 823 message is what I call a 'hard I/O error'. SQL Server asks the OS to read a page and it says no - this means the I/O subsystem couldn't read the page in question.

The CHECKDB output means that it couldn't create the internal database snapshot that it uses to get a transactionally-consistent point-in-time view of the database. There are a number of different causes of this:

  1. There may not be any free space on the volume(s) storing the data files for the database
  2. The SQL service account might not have create-file permissions in the directory containing the data files for the database

If neither of these are the case, you can create your own database snapshot and run DBCC CHECKDB on that. One you have, run the following:

DBCC CHECKDB (yourdbname) WITH NO_INFOMSGS, ALL_ERRORMSGS

If you post the results, I'll interpret them for you (I wrote DBCC CHECKDB for SQL 2005)

Whatever the results are, you're looking at either restoring from a backup, extracting data to a new database, or running repair. Each involves varying amounts of downtime and data-loss. You're also going to have to do some root-cause analysis to figure out what happened to cause the corruption in the first place.

Btw - do you have page checksums enabled? Have you looked in the SQL error log or Windows application event log for any signs of corruption or things going wrong with the I/O subsystem?

Hope this helps.

1

Ouch! Fatal error 823 indicates that there is a physical disk error. Specifically, that a page requested by SQL Server cannot be read. It's a very serious error indicating a major problem with that database or with the server in total. I hope you have a backup of the database.

I would check the eventlog on that server, system and application. See if there are any disk-related errors. What are the details of CHECKDB on the DB?

 DBCC CheckDB('< Database name >) WITH NO_INFOMSGS. 
1
  • Here's the result of "DBCC CheckDB('mydatabase') WITH NO_INFOMSGS": Msg 1823, Level 16, State 2, Line 1 A database snapshot cannot be created because it failed to start. Msg 7928, Level 16, State 1, Line 1 The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline. Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to perform the operation.
    – user10082
    Jun 19, 2009 at 10:51
0

I got the same error but It was due to privileges.Once I have given with all privileges on drive i.e. Windows Drive , Its working without any error.

1
  • We are getting this error suddenly.. Will check with system admin.. Apr 7, 2016 at 19:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .