Wednesday, November 17, 2010

Repairing database post “Msg 2570, Level 16, State…” Error

Data Purity is an option added to DBCC CHECKTABLE and DBCC CHECKDB commands to validate each column value in all the table rows of a SQL Server database. This command helps the user to ensure that the values saved in the columns are valid. And, in case the values saved in the table are invalid, then a user might encounter a 2570 error message, as listed below:

“DBCC results for 'table2'.
Msg 2570, Level 16, State 3, Line 1
Page (1:155), slot 0 in object ID 2105058535, index ID 0, partition ID 72057594038452224, alloc unit ID 72057594042449920 (type "In-row data"). Column "col2" value is out of range for data type "decimal". Update column to a legal value.
There are 4 rows in 1 pages for object "table2".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'table2' (object ID 2105058535).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'realdata'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.”

Repairing Database

The values saved in the database become invalid only when the database gets logically damaged. In such cases, the user needs to follow any of the below actions to overcome the corruption:

• Delete the entire database and restore it from an online or offline backup.
• Insert a dummy value for all the invalid records in the database.

While the above solutions can resolve the error message, there are few problems that might arise with the above resolutions:

• In case of no backup availability, all the database records will be lost.
• In case of inserting dummy records, the user might not able to decide what shall be the dummy value. And if in case the user decides, he/she might not be able to enter all the values (in case there are millions of records).

The above problems do not allow the user to overcome a 2570 error message in most cases. To surpass these problems and to access the records saved in the database, a third party SQL recovery application comes in handy.

Data Recovery Info:

Such commercial SQL database recovery tools available within a single mouse click and do not require any hard core technical skills for understanding. These tools recover almost all the records from any logically damaged SQL Server database and save them at user specified location.

Applies to

SQL Server 2005

No comments:

Post a Comment