Read time 3 minutes
Microsoft SQL Server 2005 is designed with exceptional features of table partitioning with which you can utilize to enhance the performance of large databases as these partitioned tables are flexible and easy to maintain.
DBCC CHECKTABLE command is used to keep a check at the consistency of these partitioned tables and therefore when a user runs this command and gets errors, chances are that the table has corrupted. In order to restore and refurbish complete data in it, users can utilize the last database backup. In case you come across a situation where you don’t have backup or your backup has gone corrupt, scan the damaged backup using SQL Server recovery and MDF repair software.
Consider a situation, suppose you have two partition tables namely Table A and Table B, both having the same columns and partitioned against a similar column. You create a clustered index on Table B and drop it thereby switching a partition from Table A to Table B. Then, you import suitable data to Table B by making use of BULK INSERT command with TABLOCK option. All this procedure can corrupt the data and if you run DBCC CHECKTABLE command to ensure the consistency of such table, you encounter the following error message:
Msg 2570, Level 16, State 3, Line 2 Page (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.
Additionally, you may also receive error with ID 8984 or 8988 which are mentioned as follows:
- Error message ID 8984
- Table error: object ID ObjectID, index ID IndexID, partition ID PartitionID. A row should be on partition number PartitionNum1 but was found in partition number PartitionNum2. Possible extra or invalid keys for:
- Error message ID 8988
- Row (FileID:PageNumber:RowNumber) identified by (HEAP RID = (FileID:PageNumber:RowNumber)).
Reason behind the issue
The above mentioned error messages appear due to mismatch between metadata of the two tables. When the user drops 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 this is when the user receives the data corruption errors.
Solution
Run DBCC CHECKTABLE command with repair options in order to correct the data corruption issues. In case this doesn’t solve the problem, it is suggested that the user should delete the damaged table if he has a suitable backup copy. In case of no backup or damaged backup, the best way to get back the original file is to make use of third party MS SQL repair applications.
SQL Server Recovery and recovery software is known for offering secure and sound repair services to refurbish damaged SQL databases. MDF files of MS SQL Server 2000 and 2005 are successfully repaired with data recovered and saved to new working MDF files.