Thursday 14 May 2015

Troubleshooting With Damaged Database File in SQL Server

blogger tricks

The SQL Server performs its input/output operations by using Windows API’s like ReadFile, ReadFileScatter, WriteFile and WriteFileGather. After these input/output operations, SQL server views those errors that are related to these APIs. If any of these APIs call fail with Operating System error, then SQL Server displays the Error 823.

There is a logical consistency problem encountered with input/output operations while transferring the data. Error 824 displays this logical consistency.

What is Error 824?

In SQL server, Error 824 indicates that there is a major problem in the storage system or in the hardware part. The Error is encountered when the database file is found damaged. This Error also indicates that the SQL server finds something wrong on the page while Windows didn’t find any error in the page.

Causes of Error 824

The error 824 shows the following information:

  • The suspected file of the database on which the input/output operation is executed.
  • To which database this file belongs.
  • Which page number was involved while performing input/output operation?
  • Was the performed operation a write or read operation?

As this Error indicates to issues with the storage system or the hardware part of the system, so for the solution of this problem, it is very important to look for issues in the hardware.

Inspection of Hardware Failure

First, check whether it is a hardware problem or not, if it is a hardware failure problem, then check the hardware and fix the problem. Otherwise, if it is a data corruption issue, then try to exchange various hardware components to segregate the problem.

Finally, it can be useful to change to a new hardware system. This change may include, re-formatting of the disk drives and installation of the operating system again.

Note:If the problem is not related to the hardware and you have a clean backup, then restore your database from backup.
Note:Use PAGE_VERIFY CHECKSUM option while changing the databases.

Use Following Steps To Solve The Problem Manually

  • Check the suspected pages in msdb.
  • Calibrate the database consistency.
  • Quickly turn On the PAGE_VERIFY CHECKSUM option if it is off.
  • Check the Windows Event logs for the error that is reported by the Operating System.
  • Utilize the SQLIOSim.
  • Work with your device manufacturer.
  • Evaluate the filter drivers if available.

In case the following manual method won’t work, then you should go for a third party SQL recovery tool.

No comments:

Post a Comment