Thursday, June 30, 2011

How to fix Consistency Errors in SQL Server Database?

When a database administrator executes DBCC CHECKDB or DBCC CHECKTABLE command on the SQL server database then one message is shown in SQL server error log, message looks like this:

2011-06-28 22:07:06.34 spid53 DBCC CHECKDB (database_name) executed by MYDOMAIN\the user found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001. This is an informational message only. No user action is required”.

This message shows how many consistency errors are found and repaired for SQL by the DBCC CHECKDB command.

Cause for consistency errors: DBCC CHECKDB command checks the physical and logical consistency of the database rows, pages, indexes, allocation pages and many more. If DBCC CHECKDB founds any problem with all these checks then it report errors in the error log section. There are so many reasons for these errors like hardware problem, SQL server engine problem, drivers problem, file system corruption, and many more.

How to Resolve: Four possible solutions which I found for resolving the consistency errors reported by DBCC CHECKDB…
  1. Fix hardware problem.
  2. Restore from known good backup.
  3. Run DBCC CHECKDB command with minimum repair level.
  4. Try any third party Recovery tool for SQL.
1) Fix Hardware Problem: When you found any consistency error in the SQL server database then first of all check the hardware component of your system. If you found any problem in hardware component then fix the component with new one or transfer the SQL server database.

2) Restore from known good backup: If there is no any problem with the hardware component then restore your SQL server database from known good backup. It is the best solution for resolving the consistency errors.

3) Run DBCC CHECKDB command with minimum repair level: If you are not able to restore from backup then run DBCC CHECKDB command with minimum repair level repair_allow_data_loss to repair SQL server database.

4) Try any third party Recovery tool for SQL: If all above three solutions fail to restore your database then last solution is to restore SQL server database by any SQL server recovery software. There is plenty of SQL server recovery software available in the market, it is totally depends on you to find the best software that is cheap and effective.

1 comment:

  1. This comment has been removed by a blog administrator.