KB3095958 - FIX: Metadata inconsistency error after you switch table partitions and drop corresponding files and filegroups

Symptoms

Consider the following scenario:

• You have two partitioned tables in Microsoft SQL Server 2014, and the partitions of the first table are mapped to different files and filegroups by using the same partition scheme and function.

• You switch one of those partitions to the second table, and then you truncate the second table.

• You drop files and filegroups that are mapped to the switched partition.

• You run a SELECT statement on the second table.

In this scenario, you receive the following error message:

When you run DBCC CHECKDB/CHECKTABLE, you receive the following error message:

Resolution

The issue was first fixed in the following cumulative updates of SQL Server:

• Cumulative Update 3 for SQL Server 2014 SP1

Note This fix only prevents future occurrences of this issue. If you're already experiencing this issue, export your data into a fresh database without any existing metadata corruption. To do this, follow these steps:

1. To determine whether a partition has an invalid filegroup, run the following query to see if it returns a result:
   

   SELECT * FROM sys.allocation_units AS au WHERE au.data_space_id NOT IN (SELECT data_space_id FROM sys.filegroups)

2. Make the table with the metadata corruption viewable again.
   
   
   If the query from step 1 returns a result, the partition with corrupted metadata is preventing you from viewing (select * from) any rows in the table. To work around this problem, remove that bad partition.
   
   Note The bad partition should be empty. Otherwise, the files and the filegroup that it was in could not have been dropped or deleted. 
   
   To do this, move this partition into another table that uses the same partitioning scheme. This table can be just a dummy table. Use the container_id from the query in step 1, and match it with the partition_id from sys.partitions. (Make sure that you note the partition_number.) Use the partition_number to perform an ALTER TABLE SWITCH PARTITION from the table that was unviewable to the dummy table. The dummy table should have the same set of columns and use same partition scheme. Your query to find the inconsistent partition may resemble the following:
   
   

   SELECT au.container_id, au.data_space_id, p.partition_number FROM sys.partitions AS p JOIN sys.allocation_units AS au ON p.partition_id = au.container_id LEFT JOIN sys.filegroups AS fgs ON fgs.data_space_id = au.data_space_id WHERE object_id = OBJECT_ID('MyTableName') AND fgs.data_space_id IS NULL;

3. Copy the data out from the previously unviewable table into the new database.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.