Recommended updates when you use data compression and table partitions

Gilt für: SQL Server 2008 EnterpriseSQL Server 2008 R2 Enterprise

Summary


When you use data compression and table partitions, you might change the schema of the table. For example, you might drop or add columns to the table. After this, you might notice that you encounter problems while you perform a DML query against the partitions. These problems include access violations, assertions, and database recovery failures. The problems occur because of incorrect maintenance of column information across the different partitions (partitions that were created before the schema change and partitions that were created after the schema change). Also, differences exist between what the physical rows indicate and the partition metadata.

You have to update your Microsoft SQL Server installations by using the fixes that are mentioned in the "More Information" section. Or, you have to use the alter index command to rebuild all partitions of all indexes of the affected table.

More Information


The fixes that are related to this problem are as follows:
  • 2629456 FIX: Access violation when you insert a record into a new empty partition in SQL Server 2008 or in SQL Server 2008 R2
    SQL Server 2008 Service Pack 2: 10.00.4000.00 <= sql build < 10.00.4326.00 [recommended build = 10.00.4326.00]
    SQL Server 2008 Service Pack 3: 10.00.5500.00 <= sql build < 10.00.5768.00 [recommended build = 10.00.5768.00]
    SQL Server 2008 R2 RTM: 10.50.1600.1 <= sql build < 10.50.1809.00 [recommended build = 10.50.1809.00]
    SQL Server 2008 R2 Service Pack 1: 10.50.2500.0 <= sql build < 10.50.2796.00 [recommended build = 10.50.2796.00]
  • 2504090 FIX: Access violation when you insert data into a new partition of a partitioned table after you drop a column of the table in SQL Server 2008 R2 or in SQL Server 2008
    SQL Server 2008 R2 RTM: 10.50.1600.1 <= sql build < 10.50.1777.00 [recommended build = 10.50.1777.00]
    SQL Server 2008 R2 Service Pack 1: 10.50.2500.0 <= sql build < 10.50.2769.00 [recommended build = 10.50.2769.00]
  • 2548593 FIX: Database corruption if data compression enabled on a partitioned table in SQL Server 2008
    SQL Server 2008 Service Pack 1: 10.00.2531.00 <= sql build < 10.00.2821.00 [recommended build = 10.00.2821.00]
    SQL Server 2008 Service Pack 2: 10.00.4000.00 <= sql build < 10.00.4285.00 [recommended build = 10.00.4285.00]
The following table provides more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product against which the rule is evaluated.
Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated
System Center AdvisorSQL Server missing recommended updates when using data compression and table partitioning System Center Advisor checks if this instance of SQL Server contains databases that have tables that are enabled for data compression and for table partitioning. System Center Advisor also checks if this instance of SQL Server contains the relevant fix. If the fix is not present, System Center Advisor generates an error message. Review the details that are provided in the "Information Collected" section of the error message, and then follow the steps that are mentioned in this article to resolve the error. SQL Server 2008

SQL Server 2008 R2