Microsoft SQL Server 2005 (both the Enterprise and Developer Editions) is designed with paramount features and one of its bright features is table partitioning that the user can utilize to make improve the performance of the large databases since the partition tables are flexible in nature and therefore are easy to operate and maintain.

The DBCC CHECKTABLE is a command which is used to check the consistency of these partitioned tables and so when the user runs this command and get errors then the chances are that these tables have been corrupted. To Restore the complete information, you should use your last database backup but in case the backup are also have errors or corruption or otherwise you have not any backup then the only way is to scan the damaged database with the help of MS SQL Database Repair.

A Situation of Partition Table Corruption

Suppose you have two partition tables X and Y and both have same columns and partitioned against similar column. On table Y you have build a clustered index and drop it thereby switching a partition from Table X to Table Y. Then with the help of BULK INSERT command with TABLOCK option you have import suitable data to Table Y. All these steps are able to corrupt the data and if you run the DBCC CHECKTABLE command then you will get 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.”

Other than these you can also get the Error with ID 8984 and 8988.

These error messages can appear due to the mismatch between metadata of the two tables. When the user drops the clustered index to Table Y, its metadata is changed and after this when you switch the partitions between tables, the metadata information mismatches and therefore the user get the error message.

To solve this problem you need to run the DBCC CHECKTABLE command since it can correct the data corruption issues. If it does not worked then check if you have the backup copy of the partition table or not. If you have the backup copy then simply delete the partition table and restore from the backup copy. If unfortunately you don’t have the backup copy then the only suggested way to solve this problem of SQL 2005 Partition Table Error is by using the MS SQL Database Repair application.

To use this application, download the MS SQL Database Repair and install it on your computer system. Then scan the SQL database and it will solve the problem of damaged SQL database. The MDF files will also be repaired with this and finally you will get your MS SQL 2005 working properly.