SQL Server 2005 (Enterprise and Developer editions) supports a unique feature of table partitioning; using which you can increase the performance of large databases. Partitioned tables are flexible, easy to maintain and give high performance. DBCC CHECKTABLE command can be used to check the consistency of such tables. If you run this command and get errors, the table might be corrupt. To restore complete information, you should use your last database backup. But if you observe backup with errors or don’t have any backup, scan your damaged database using SQL database repair software.

Consider a practical scenario, you are using two partitioned tables (Table A and Table B), both having the same columns and partitioned against the same column. You create a clustered index on Table B and drop it and then switch a partition from TABLE A to TABLE B. After this, you import valid data to TABLE B using BULK INSERT command with TABLOCK option. This corrupts the data and when you run DBCC CHECKTABLE command to check the consistency of such table, you receive the similar error message:

“Msg 2570, Level 16, State 3, Line 2
age (PageID), slot SlotID in object ID ObjectID, index ID IndexID, partition ID PartitionID, alloc unit ID UnitID (type “In-row data”). Column “ColumnName” value is out of range for data type “DataType”. Update column to a legal value.”

You might also receive error with ID 8984 or 8988.

Cause

The above behavior is observed due to mismatch between metadata of the two tables. When you drop the clustered index of TABLE B, its metadata gets changed. After this, when you switch the partitions between tables, the metadata information gets mismatched and you receive data corruption errors, as above.

Solution

Follow these methods to correct data corruption issues:

  • Run DBCC CHECK TABLE command with repair options
  • If problem persists, delete the damaged table if you have valid database backup
  • In case of no backup availability or backup invalid state, make use of commercial SQL Repair applications. These are recommended SQL Recovery applications that safely repair damaged SQL databases. Also, they provide interactive interface.

Stellar Phoenix SQL Recovery is the finest tool to repair damaged SQL Server databases. It supports SQL Repair for SQL Server 2000 and 2005. This software provides self-descriptive interface and restores all database objects including tables, user defined data types and functions, stored procedures, triggers, views, rules and others. This SQL Recovery software is compatible with Windows Vista, XP, 2003, 2000, and NT.