This handout is going to help you out to solve the fatal error 823 in MS SQL Server and it will also make you understand the cause and layout of the error. It’s a crucial issue, somewhere at a point we all have encountered it, so hope it is going to help you out
What is ERROR 823 in MS SQL Server?
Whenever a software/application tries to fetch some data, it accesses a certain Database. The Software can perform various functions over Database like:
- Sending data.
- Retrieving/fetching data.
- Updating data.
- Deleting data.
These are the I/O operation which a software/application can perform over a database. Whenever there is a problem during these I/O operations than there could be an exceptional case which can cause the error (823).
- SQL server uses an API, which communicates with Windows OS. The server keeps checking the API’s and if it finds any inconsistency it (Server) generates Code 823 Fatal Error.
- Operating System can also be a reason for the error, which could be due to the Hardware issue. It could be the fragmented Hard-disk also or corrupted hard-disk.
FATAL ERROR 823 Layout:
In the above image, there is an error popup box where the error message is displayed. There are some underlined strings which are the key points of this message. These points represent some information:
BLACK Line: It is the I/O Check failure or an OS error. In the case of OS Error, OS Error number follows “I/O Error”.
RED Line: This contains either Read or Write, which shows the operation during which the error occurred.
BLUE Line: This is the offset number which represents the Physical file offset from the start of the page. In easy words, by dividing this offset by 8192 you can get the logical page no. which is affected by the error.
GREEN Line: This is the MDF File with .mdf extension, which is associated with the I/O issue.
Concept of .mdf file and .ndf file:
MDF File: It’s a Primary Data file which contains objects of database like tables, views, stored procedures, triggers etc.
NDF File: It’s a Secondary Data file, there is a possibility that any database doesn’t have a single secondary file or may have multiple secondary files.
How to Fix Fatal Error 823:
It’s not a big deal to resolve this error but it needs a little understanding of commands and hardware. To solve this error there is a command which comes handy that is DBCC CHECKDB. This command checks the logical and physical integrity of all objects in a specific database.
1. Run “DBCC CHECKDB(DATABSE_NAME,REPAIR_REBUILD);” TSQL statement.
Note: The Database should be in Single-user Mode if it is not then run another TSQL statement “ALTER DATABASE DB2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;”
2. Run”DBCC CHECKDB” again to check if the database repaired or not.
If the problem remains same either it could be a hardware issue then you need an IT member to resolve your hardware issue or it could be in need of database restoration. After the restoration of the database file, check again by running statement CHECKDB.
For making it easy you can go for some automated tools also which can be helpful and as well as easy to handle. I went through the internet and found a hell out of tools available to restore your database or to fix fatal error 823 and after doing a lot of comparisons and research the tool which I’ve found was “MSSQL Recovery Tool”. The tool is able to repair the corrupted mdf and ndf files of the database And is supported by various versions of the windows.