Here's an interesting bug that surfaced recently, first reported by Bryan Smith on the MSDN disaster recovery/HA forum three weeks ago.
One of the mechanisms I advise for running consistency checks on VLDBs with multiple filegroups is to run successive DBCC CHECKFILEGROUP operations on the filegroups holding the partitions (see CHECKDB From Every Angle: Consistency Checking Options for a VLDB). Prior to SQL Server 2005 SP3, if a table or index is split into multiple partitions, then DBCC CHECKFILEGROUP would skip checking the entire table or index if it was partitioned over multiple filegroups. From SQL Server 2005 SP3 onwards, DBCC CHECKFILEGROUP will validate only the partitions of tables and indexes that reside on the filegroup being checked – rather than skipping the whole table or index – a big improvement.
Now it seems that SQL Server 2008 has a bug where it essentially has regressed back to the old behavior where DBCC CHECKFILEGROUP will skip a table or index if it's not wholely contained on the filegroup being checked.
Here's a script you can use to test this. It creates a partitioned table over multiple filegroups and then runs DBCC CHECKFILEGROUP on the first partition. I'll discuss the results after the script.
CREATE DATABASE DBMaint2008;
GO
USE DBMaint2008;
GOALTER DATABASE DBMaint2008 ADD FILEGROUP DataPartition1;
ALTER DATABASE DBMaint2008 ADD FILEGROUP DataPartition2;
ALTER DATABASE DBMaint2008 ADD FILEGROUP DataPartition3;
GOALTER DATABASE DBMaint2008 ADD FILE
(NAME = N'DataPartition1', FILENAME = N'C:\SQLskills\DataPartition1.ndf',
SIZE = 10, FILEGROWTH = 10)
TO FILEGROUP DataPartition1;
GO
ALTER DATABASE DBMaint2008 ADD FILE
(NAME = N'DataPartition2', FILENAME = N'C:\SQLskills\DataPartition2.ndf',
SIZE = 10, FILEGROWTH = 10)
TO FILEGROUP DataPartition2;
GO
ALTER DATABASE DBMaint2008 ADD FILE
(NAME = N'DataPartition3', FILENAME = N'C:\SQLskills\DataPartition3.ndf',
SIZE = 10, FILEGROWTH = 10)
TO FILEGROUP DataPartition3;
GOCREATE PARTITION FUNCTION Partitions_PFN (INT)
AS RANGE RIGHT FOR VALUES (1000, 2000);
GOCREATE PARTITION SCHEME [Partitions_PS]
AS PARTITION [Partitions_PFN] TO (DataPartition1, DataPartition2, DataPartition3);
GOCREATE TABLE TestTable (c1 INT IDENTITY, c2 DATETIME DEFAULT GETDATE ());
CREATE UNIQUE CLUSTERED INDEX TestPK ON TestTable (c1) ON Partitions_PS (c1);
GOSET NOCOUNT ON;
GO
INSERT INTO TestTable DEFAULT VALUES;
GO 3000DBCC CHECKFILEGROUP (DataPartition1);
GO
On SQL Server 2005 SP3, the output from the final batch contains:
DBCC results for 'TestTable'.
Cannot process rowset ID 72057594038452224 of object "TestTable" (ID 2073058421), index "TestPK" (ID 1), because it resides on filegroup "DataPartition2" (ID 3), which was not checked.
Cannot process rowset ID 72057594038517760 of object "TestTable" (ID 2073058421), index "TestPK" (ID 1), because it resides on filegroup "DataPartition3" (ID 4), which was not checked.
There are 999 rows in 3 pages for object "TestTable".
This shows that it processed the 1000 rows in the first partition, but not the other two – as we'd expect.
On SQL Server 2008, the output for TestTable is limited to:
Cannot process rowset ID 72057594038910976 of object "TestTable" (ID 2105058535), index "TestPK" (ID 1), because it resides on filegroup "DataPartition2" (ID 3), which was not checked.
And that's it – nothing about it processing any rows in partition 1. This shows that DBCC didn't process the first partition as we'd expect – this becomes even more apparent with very large amounts of data, where DBCC CHECKFILEGROUP will just complete almost instantly.
As Bryan says in his post, Microsoft has acknowledged this is a bug and it should hopefully be fixed for 2008 CU5. In the meantime, this is something you should be aware of as your tables may not be being checked properly.
2 thoughts on “DBCC CHECKFILEGROUP bug on SQL Server 2008”
Do you know if this was ever fixed? I have this issue on build 10.50.2811. SQL 2008R2 SP1 CU6.
thanks
Paul Drumm
It was fixed in 2008 SP1 CU6 so should be in 2008R2 as well. You’ll need to check with CSS/your MS rep to see which 2008R2 build it’s in.