Last summer we discovered that some records that had been migrated to a read-only filegroup via partitioning were still getting updated (here’s the sordid details on stack overflow). Working with the SQLCat team at PASS in October we discovered that while the partitioning scheme indicated the records should be on the read-only partition according to the allocation unit mapping showed the records will still resident on the active filegroup. For unknown reasons during partition maintenance the records hadn’t moved properly. Working with MS Support we weren’t able to replicate the issue or determine how it happened. Even more insidious is that there is no good way to detect if the issue is occurring unless you go specifically looking for it. It only came to our attention because we were setting some records to read only. If you are doing partition maintenance which moves records physically on the disk you might be interested in the following query which will detect when there is a disconnect between where your partition scheme thinks records will reside and where sys.allocation_units tells you those same records reside:


/* returns all partitioned tables: */
SELECT OBJECT_NAME(p.[object_id]) AS 'TableName'
, i.[name] AS 'IndexName'
, i.index_id
, p.[object_id]
, au.data_space_id
, p.partition_number
, p.hobt_id
, partitionds.name AS partition_filegroup
INTO #partDist
FROM sys.allocation_units au
JOIN sys.partitions p ON au.container_id = p.[partition_id]
LEFT JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
LEFT JOIN sys.data_spaces partitionds ON dds.data_space_id = partitionds.data_space_id
LEFT JOIN sys.indexes i ON p.[object_id] = i.[object_id]
JOIN sys.partition_schemes ps ON dds.partition_scheme_id = ps.data_space_id
AND i.data_space_id = dds.partition_scheme_id
LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE i.index_id = ( SELECT TOP 1
i.index_id
FROM sys.indexes inner_i
WHERE inner_i.[object_id] = i.[object_id]
);

/* returns allocation unit details for partitioned tables: */
SELECT OBJECT_NAME(p.[object_id]) AS ‘Name’
, p.index_id
, p.[object_id]
, au.data_space_id
, p.partition_number
, p.hobt_id
, partitionds.name AS partition_filegroup
INTO #allocUnits
FROM sys.allocation_units au
JOIN sys.partitions p ON au.container_id = p.[partition_id]
LEFT JOIN sys.data_spaces partitionds ON au.data_space_id = partitionds.data_space_id
WHERE p.[object_id] IN ( SELECT [object_id]
FROM #partDist )
ORDER BY Name
, p.partition_number;

/* Check for records where partition numbers match but partition filegroup names do not: */
SELECT pd.TableName
, pd.IndexName
, au.data_space_id ‘AllocDataSpace’
, pd.data_space_id ‘PartDataSpace’
, au.partition_number ‘AllocPartNum’
, pd.partition_number ‘PartNum’
, au.partition_filegroup ‘AuFilegroup’
, pd.partition_filegroup ‘PartFilegroup’
INTO #erroralert
FROM #allocUnits au
JOIN #partDist pd ON au.object_id = pd.object_id
AND au.partition_number = pd.partition_number
AND pd.index_id = au.index_id
WHERE au.partition_filegroup <> pd.partition_filegroup
ORDER BY pd.TableName
, au.index_id
, au.partition_number;

IF EXISTS ( SELECT *
FROM #erroralert )
BEGIN
SELECT DISTINCT TableName, AllocDataSpace,PartDataSpace,AllocPartNum, PartNum, AuFilegroup, PartFilegroup
FROM #erroralert;
PRINT ‘ALERT DBA Here’;
END;