Tuesday, March 8, 2011
SQL Server Database Consistency Check Errors are Reported by DBCC CHECKDB
Microsoft SQL Server includes an inbuilt utility, called DBCC CHECKDB, to check the consistency and integrity of MDF (Master Database File). However, sometimes the DBCC CHECKDB process does not complete successfully and can not fix the associated issues. Database inconsistency can make the SQL Server database inaccessible and lead to severe data loss situations. In such cases, you need to recover SQL database, in order to extract data from it. When you execute DBCC CHECKDB on an SQL database, an error message similar to the following one, appears in ERRORLOG of Microsoft SQL Server- “2010-03-31 22:07:06.34 spid53 DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser 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.” The above message shows the total number of database consistency errors in SQL database and how many of them are repaired. The message information, start from 'internal database snapshot...', appears only if you run the DBCC CHECKDB online. The database consistency check process is halted with this error message and your database remains inaccessible. To gain access of your valuable data, SQL database recovery is required. Root of the problem: DBCC CHECKDB utility checks the logical and physical consistency of SQL Server database pages, allocation pages, rows, system table referential integrity, index relationships, and other database structure checks. In case any one of these checks does not complete successfully (depending upon the options that you have selected), errors are reported as part of this command. This behavior may occur due to numerous reasons of database corruption, such as hardware system problems, file system corruption, damaged pages in memory, or issues with MS SQL Server Engine. Resolution: The most excellent solution to fix DBCC CHECKDB consistency errors is to restore the database from the most recent backup. However, if backup is not available or updated, CHECKDB offers an option to repair the errors. When all else fails, SQL recovery software are required to repair and restore the damaged SQL Server database. They employs high-end scanning techniques to thoroughly scan entire database and extract all of its inaccessible objects. The MS SQL recovery tools are pretty safe and easy to use. Stellar Phoenix SQL Recovery software repairs damaged or corrupt SQL Server database and restores it to default or user-specified location. It is usable with Microsoft SQL Server 2008, 2005, and 2000. The software extracts all items from SQL Server database, such as tables, reports, macros, forms, stored procedures, triggers, and constraints.