Search

Thursday, January 6, 2011

Fixing 2537 Corruption Errors in SQL Server Reported by DBCC CHECKDB

Microsoft SQL Server is one of the most widely accepted RDBMS (Relational Database Management System), developed and marketed by Microsoft Corporation. It stores your mission-critical data in a database, which has two major data files - MDF (Master Database File) and NDF (Next Database File). In some situations, the SQL Server database may encounter inconsistency issues and become inaccessible. This behavior renders your precious data inaccessible and causes serious data loss. In order to overcome such issues, you need to go for SQL database recovery alternatives.

Microsoft SQL Server provides DBCC CHECKDB tool for checking the consistency and integrity of the database. It may report the inconsistency of the database if all the below mentioned conditions are true:

  • MS SQL Server loads the data to any column with sql_variant data type.

  • There are some character values in sql_variant column.

  • The information is sent either to another SQL Server directory or through intermediate file.

  • The information develops in some other column with sql_variant data type, and simultaneously it is sent by SQL Server on-the-wire.

The DBCC CHECKDB statement may report you the following corruption error:

“Msg 2537, Level 16, State 43, Server MYSERVER, Line 1 Table error: Object ID 1977058079, index ID 0, page (1:75), row 0. Record check (Valid SqlVariant) failed. Values are 4 and 0. DBCC results for 'MyTable'. There are 0 rows in 1 pages for object 'MyTable'. CHECKDB found 0 allocation errors and 1 consistency errors in table 'MyTable' (object ID 1977058079). CHECKDB found 0 allocation errors and 1 consistency errors in database 'MyReproDest'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MyReproDest ).”

Cause:

This problem occurs due to corruption of the Microsoft SQL Server database table or entire database. You should not run the REPAIR_ALLOW_DATALOSS statement on such databases as it may remove the affected data.

Resolution:

In order to recover SQL Server database in such cases, you should try restoring the database from updated backup. Backup is the easiest and the most effective way to prevent data loss.

However, in case backup is not updated or available, commercial SQL Serverrecovery software becomes need of hour. The advanced third-party tools enable you to scan entire database using powerful MS SQL recovery scanning algorithms and restore entire database.

1 comment: