Ошибка 823 серьезность 24 состояние 2

What is SQL error 823?

A SQL Server database encounters error 823 when an I/O operation (read or write request) fails. This usually happens due to issues with the operating system, drivers, or hardware issues. The SQL error 823 may also occur due to inconsistencies in the file system or a corrupted database file.

The complete text of the SQL 823 error message looks similar to:

In the error message, 823 is the error code. The severity level 24 means that the error occurred due to problems with the hardware or software. The state 2 is used to differentiate the error from other errors with the same number but in a different state.

Further, the error provides the following information:

• The data file (usually a .MDF file) against which the I/O (read or write) operation was performed.
• The offset, i.e., the physical byte offset from the start of the file, dividing it by 8192 will help you find the logical page number that is corrupted due to the error.
• Identifies whether a read or write request was made to a device.

What problems are associated with the 823 IO error?

This problem can be related to the following problems:

1. A Torn Page
2. Bad Page ID
3. Insufficient bytes transferred

What is a torn page?

It is a page that was incorrectly written. We could say Torn Page Detection writes a bit for every 512 bytes on the page. It helps detect a page that is not successfully written to disk. But, it does not tell if the data stored to disk is actually correct as a couple of bytes may have been written incorrectly.

What is the error message for the torn page error?

The error will be like this one:

How can I detect torn page IO error?

You can detect these errors when you run the CHECKDB command in your database.

What is Bad Page ID?

It is when the header page ID is not the same as the expended one when reading in the disk.

Why is the error insufficient bytes transferred produced in SQL Server?

It means that the API call was invoked successfully, but the bytes transferred are not the expected ones.

How to solve SQL Server error 823 ?

The first step would be to run a DBCC CHECKDB command and check if it returns consistency errors. If it does, try to restore your database from a backup. Once the database is restored, run a CHECKDB to verify that the problem is solved.

If the problem persists or backup is not available, try to repair the database using the following T-SQL statement:

DBCC CHECKDB (DB_NAME, REPAIR_REBUILD);

To repair the database, you must restrict its usage to a single user. This requires changing the database state to Single-user mode. To do so, run the following T-SQL query:

ALTER DATABASE DB2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

You can also set the database to a Single-user using the point-in-click interface in SQL Server Management Studio (SSMS). For this, right-click on the database in SSMS and go to the Options page. On the ‘Options’ page, choose the SINGLE USER option from the Restrict Access drop-down box, then click OK.

After setting the database to Single_user mode, repair the database, and run ‘DBCC CHECKDB’ again to verify that the database was repaired.

Note: If the error persists, an issue with the hardware might have caused SQL error 823. In that case, you will need to repair your hard disk. Contact the IT members in charge of the hardware. Check also if there are fragmentation problems in the hard disk.

If you have a Torn page error, you can use the CHECKSUM and the torn page detection to repair and verify the errors.

What Else You Can Do to Resolve SQL Error 823?

Use Stellar Repair for MS SQL to fix database consistency errors reported by DBCC CHECKDB and preview the recoverable data. This SQL repair tool  can be downloaded from the link below.

This software will repair the .MDF/.NDF files of SQL Server database. Before using the software, make sure to stop the SQL Server Service first. It is recommended to stop the service using the SQL Server Management Studio (SSMS) or the SQL Server Configuration Manager.

Now follow these steps to repair and recover your SQL database file:

• Browse or search the corrupt .MDF file.
• Once the .MDF file is uploaded, press the Repair button.

Note: If you have one or multiple secondary data files (.NDF file) associated with your database, then put all the NDF files at the location of the MDF file. Once everything is done, you can restart the SQL Server Service and rerun the CHECKDB command.

• Run standard scan to repair the selected database file quickly.

• Preview the repairable database objects and select all or specific objects you want to restore. Next, press the Save button from the File menu.

• Enter the details as shown in the following screen to complete the file saving process.

Once the software repairs the .MDF file, you will no longer see the SQL Server error 823.

Conclusion

This blog discussed the reasons that could result in SQL Server database error 823. It can be caused by hardware (disk errors, hardware or software failures) or system problems. Also, the blog explained how to fix the SQL 823 error by restoring the database from backup, running DBCC CHECKDB with a repair option, or using Stellar Repair for MS SQL software. Stellar Repair for MS SQL is a simple SQL repair tool. If you have any queries, please do not hesitate to write your questions and comments.

Are you seeking for a solution of SQL error 823? We can help you fix it.

Often, the SQL server error 823 occurs when there is a problem in SQL Server. It can either due to fault in the operating system error or an IO error.

Here at Bobcares, we have seen several such SQL errors as part of our Server Management Services for web hosts, Linux/Windows users, and online service providers.

Today, we’ll take a look at the cause for this error and see how to fix it.

Reasons for SQL Error 823?

SQL Server uses Windows APIs (ReadFile, WriteFile, ReadFileScatter, WriteFileGather) to perform file I/O operations.

Further, the SQL Server uses Application Programming Interface or API to perform file I/O operations in Windows OS. Once finishes I/O operations, SQL Server tests any error conditions related to these API calls

However, if an API calls fail with an Operating System error results in SQL Error 823. Mostly, it happens due to operating system error or an IO error.

Further, the 823 error message contains the following information:

1. A database file that was used for the I / O process.

2. The offset within the file where you tried the I / O process.

3. Indicates if the I/O operation is a read or write request.

4. The Operating System error code and error description.

Also, other reasons behind the API-Server incompatibility are;

1. User errors in the system.

2. System corruption.

3. Database corruption.

4. SQL Server update or reinstallation.

5. Hardware corruption.

How to fix SQL Error 823

Here, let’s see the different solutions provided by our Support Engineers to fix this error.

Operating system error

SQL Server reports an operating system error if a read or write Windows API call is not successful.

And, the error message looks like,

Error: 823, Severity: 24, State: 2.
2010-03-06 22:41:19.55 spid58 The operating system returned error 1117 (The request could not be performed because of an I/O device error.) to SQL Server during a read at offset 0x0000002d460000 in file 'e:program filesMicrosoft SQL Servermssqldatamydb.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. It is recommended to complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

In order to fix the error, our Support Engineers check the suspect_pages table in MSDB for other pages that having this problem.

Also, verify the consistency of databases located on the same volume using DBCC CHECKDB command. Otherwise, the consistency errors can be eliminated by restoring data from a good backup.

An IO error

Frequently, this error can be related to the following problems,

1. A Torn Page.
2. Bad Page ID.
3. Insufficient bytes transferred.

So, our Support Engineers initially run a DBCC CHECKDB command. then, we try to repair the database using the below command.

DBCC CHECKDB (DB_NAME, REPAIR_REBUILD);

Again, we run “DBCC CHECKDB” to verify whether the database is repaired.

If repairing ends in failure, we restore the database with a backup. Once the database is restored, run a CHECKDB to verify that the problem is solved.

Sometimes, the problem can persist after the restoration or repairing. Mostly, this happens due to a hardware problem, then the customer may need to repair the hard disk.  Or, they need to contact the hardware vendor or device manufacturer to ensure the following,

1.  Firstly, check whether the hardware devices and the configuration conforms to the I/O requirements of SQL Server.

2. Then, the device drivers and other supporting software components for devices in the I/O path are not outdated.

[Need assistance to SQL error 823? We’ll help you.]

Conclusion

In this article, we learned different reasons that produced the SQL Server error 823. Mostly, this problem can be caused by hardware (disk errors, power failures) or system problems. Today, we saw how our Support Engineers provide a work-around for this error.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

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.