Update 2009-12-09: Commulative Update 8 for SQL Server 2008 and a subsequent Hotfix after Commulative Update 5 for SQL Server 2008 SP1 have corrected this issue. So if you haven’t been able to run DBCC CHECKFILEGROUP’s go grab these!

I noticed Cumulative Update 4 for SQL Server 2008 is out now. In it I found not one but two issues fixed that affect me. Thankfully, both issues have a relatively minor impact on my systems. 

However, the fix that I want to see, is not in this Cumulative Update. It’s a fix for DBCC CHECKFILEGROUP not working on file groups that are part of a partitioned table.

DBCC CHECKFILEGROUP runs the normal DBCC CHECKDB commands, but only on the specified file group. This is quite handy when you’re dealing with a very large database (VLDB); say one in the multi terabyte range, because a CHECKDB command can take an awful long time to run on huge files. Thus one common practice, championed by Paul Randal is to run DBCC CHECKFILEGROUP regularly on each file group. This lets you break up a really long process into manageable chunks. 

Of course, what I did not know until recently is that DBCC CHECKFILEGROUP doesn’t work in right in SQL Server 2008. In fact it just skips the specified file if it is part of a partition. In my case I have a 13TB database that uses partitioning heavily. After conversion from 2005 to 2008 I went to run my DBCC checks, as I normally do, to find that it wasn’t doing the check. File groups that would take 40 minute returned in seconds and upon examination of the output the file group was being skipped. 

So blissfully thinking that someone else must have seen this I did some Google Kung-Fu … and found nothing. 

Then I did some posting on forums … and found nothing (well I did find some nice MVP’s). 

So then I called up Microsoft Premier Support, gave them my repro script and found out that I had found a bug.

Here’s the script in question, so you can try it out. This is for SQL Server 2008 RTM and SP1, Enterprise and Developer editions, and I’ve tried it out on Windows 7, Server 2003 and Server 2008. 

USE [master]

GO

-- -------------------------------------------------------------

– Create the test database

-- -------------------------------------------------------------

CREATE DATABASE [TestFileGroup] ON  PRIMARY (

      NAME = N’TestFileGroup’,

      FILENAME = N’J:\Data_Staging\MSSQL10.STAGING\MSSQL\Data\TestFileGroup.mdf’ , — Update the path

      SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB

),

FILEGROUP [FG_1]

(

      NAME = N’FG_1′,

      FILENAME = N’J:\Data_Staging\MSSQL10.STAGING\MSSQL\Data\FG_1.ndf’ , — Update the path

      SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB

),

FILEGROUP [FG_2]

(

      NAME = N’FG_2′,

      FILENAME = N’J:\Data_Staging\MSSQL10.STAGING\MSSQL\Data\FG_2.ndf’ , — Update the path

      SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB

)

LOG ON

(

      NAME = N’TestFileGroup_log’,

      FILENAME = N’J:\Logs_Staging\MSSQL10.STAGING\MSSQL\Data\TestFileGroup_log.ldf’ , — Update the path

      SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%

)

GO

 USE [TestFileGroup]

GO

 – ————————————————————-

– Create the partition function

– ————————————————————-

CREATE PARTITION FUNCTION [myRangePF](int)

AS RANGE LEFT FOR VALUES (5)

GO

 – ————————————————————-

– Create the partition scheme

– ————————————————————-

CREATE PARTITION SCHEME [myRangePS]

AS PARTITION [myRangePF]

TO ([FG_1], [FG_2])

GO

 – ————————————————————-

– Create a partitioned table on myRangePS

– ————————————————————-

CREATE TABLE [dbo].[myPartitionTest]

(

      [Id] [int] IDENTITY(1,1) NOT NULL,

      [Data] [varchar](10) NULL

) ON myRangePS (Id)

 – ————————————————————-

– Insert Test Data, Enough to cross both partitions

– ————————————————————-

INSERT INTO myPartitionTest

VALUES (‘Test 1′)

 INSERT INTO myPartitionTest

VALUES (‘Test 2′)

 INSERT INTO myPartitionTest

VALUES (‘Test 3′)

 INSERT INTO myPartitionTest

VALUES (‘Test 4′)

 INSERT INTO myPartitionTest

VALUES (‘Test 5′)

 INSERT INTO myPartitionTest

VALUES (‘Test 6′)

 INSERT INTO myPartitionTest

VALUES (‘Test 7′)

 INSERT INTO myPartitionTest

VALUES (‘Test 8′)

 SELECT * FROM myPartitionTest

 – ————————————————————-

– Run DBCC CHECKFILEGROUP

– ————————————————————-

DBCC CHECKFILEGROUP (‘FG_1′)

/*

      Expect it to show it found 5 rows on the object myPartitionTest and also to say it couldn’t check FG_2.

*/

 DBCC CHECKFILEGROUP (‘FG_2′)

/*

      Expect it to show it found 3 rows on the object myPartitionTest and also to say it couldn’t check FG_1.

*/

As you can see, the script is fairly simple. It creates a database with a couple of extra file groups, then proceeds to make a partition function and scheme with a table to go use them. Then it adds some data to the table, so data will reside on both partitions. Finally it does a DBCC CHECKFILEGROUP command and this is where it gets interesting.

What should happen when you run CHECKFILEGROUP (‘FG_1’) is that it will say it cannot check data on FG_2, Primary, etc, but will return results for the checks it ran on FG_1. But what actually happens is it returns what it can’t check and doesn’t do any checks on FG_1.

So there you have it, I’m hoping that Cumulative Update 5 will have the fix I need. I know a few others who run VLDB’s and this is disappointing news for them as well. Hopefully, this will get enough attention and will warrant a quick fix.