Error 2570 in SQL Server

SQL Server includes a database check utility called DBCC CHECKDB. In SQL Server 2005, you can use this command with DATA_PURITY option enabled that allows you to perform ‘data purity’ validations on each column value in all rows of a table or multiple tables from the database. If you run this check and it outputs error messages that indicates problems with data and hence database integrity. In such situations, you need to use your recent backup to restore the lost information. But, if you find it invalid or inappropriate to deliver required results, SQL Server Recovery tools can be used as a solution.

Consider a scenario, you check your database integrity using DBCC CHECKDB with DATA_PURITY option enabled and it gives the below error message:

“Msg 2570, Level 16, State 2, Line 1
Page %S_PGID, slot %d in Object %d Index %d Column %.*ls value %.*ls is out of range for data type "%.*ls".  Update column to a legal value.”

This error message occurs for every row that contains an invalid column value.

Cause

The above error occurs due to invalid data in a column of a table as invalid floating point values as SNAN, QNAN, NINF, ND, PD, PINF etc. It can also happen due to out-of-range data or hardware issues.

Solution

To correct the above issue, you need to follow any of the below methods:

You should find the affected rows and manually update them with correct values. To do this, you can execute T-SQL (Transact-SQL) queries against the table or alternatively, refer to the information provided by error 2570. You can set it to any of acceptable default or specific value.

Note: You can’t run DBCC command to repair the database as it can’t determine
the value to be placed instead of invalid column value.

In case if affected rows are large in number and it is not possible to update them manually, you can restore them from backup
If the above measure is not feasible because of backup unavailability or corruption, you need to use MDF Recovery applications to safely repair and restore your database. Such MDF Repair software use powerful scanning algorithms and offer advanced repair and restoration options together with interactive user interface.

Stellar Phoenix SQL Recovery is the foremost SQL Repair tool to repair damaged SQL databases. With self-descriptive interface, advanced repair options and read-only nature, it is an effective tool to repair all database objects. It supports SQL Server 2005 and SQL Server 2000. The software is compatible with Windows Vista, XP, 2003, 2000 and NT.