{"id":791,"date":"2009-09-23T01:54:00","date_gmt":"2009-09-23T01:54:00","guid":{"rendered":"\/blogs\/paul\/post\/DBCC-CHECKFILEGROUP-bug-on-SQL-Server-2008.aspx"},"modified":"2017-04-13T09:51:41","modified_gmt":"2017-04-13T16:51:41","slug":"dbcc-checkfilegroup-bug-on-sql-server-2008","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/","title":{"rendered":"DBCC CHECKFILEGROUP bug on SQL Server 2008"},"content":{"rendered":"

\nHere's an interesting bug that surfaced recently, first reported by <\/font>Bryan Smith<\/font> on the <\/font>MSDN disaster recovery\/HA forum<\/font><\/a> three weeks ago. <\/font>\n<\/p>\n

\nOne of the mechanisms I advise for running consistency checks on VLDBs with multiple filegroups is to run successive DBCC CHECKFILEGROUP<\/font> operations on the filegroups holding the partitions (see <\/font>CHECKDB From Every Angle: Consistency Checking Options for a VLDB<\/font><\/a>). Prior to SQL Server 2005 SP3, if a table or index is split into multiple partitions, then DBCC CHECKFILEGROUP<\/font> would skip checking the entire table or index if it was partitioned over multiple filegroups. From SQL Server 2005 SP3 onwards, DBCC CHECKFILEGROUP<\/font> 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. <\/font>\n<\/p>\n

\nNow it seems that SQL Server 2008 has a bug where it essentially has regressed back to the old behavior where DBCC CHECKFILEGROUP<\/font> will skip a table or index if it's not wholely contained on the filegroup being checked. <\/font>\n<\/p>\n

\nHere's a script you can use to test this. It creates a partitioned table over multiple filegroups and then runs DBCC CHECKFILEGROUP<\/font> on the first partition. I'll discuss the results after the script. <\/font>\n<\/p>\n

\n

\n\tCREATE DATABASE DBMaint2008;
\n\tGO
\n\t<\/font>USE DBMaint2008;
\n\tGO<\/font>\n\t<\/p>\n

\n\tALTER DATABASE DBMaint2008 ADD FILEGROUP DataPartition1;
\n\tALTER DATABASE DBMaint2008 ADD FILEGROUP DataPartition2;
\n\tALTER DATABASE DBMaint2008 ADD FILEGROUP DataPartition3;
\n\tGO<\/font>\n\t<\/p>\n

\n\tALTER DATABASE DBMaint2008 ADD FILE
\n\t   (NAME = N'DataPartition1', FILENAME = N'C:\\SQLskills\\DataPartition1.ndf',
\n\t   SIZE = 10, FILEGROWTH = 10)
\n\tTO FILEGROUP DataPartition1;
\n\tGO
\n\t<\/font>ALTER DATABASE DBMaint2008 ADD FILE
\n\t   (NAME = N'DataPartition2', FILENAME = N'C:\\SQLskills\\DataPartition2.ndf',
\n\t   SIZE = 10, FILEGROWTH = 10)
\n\tTO FILEGROUP DataPartition2;
\n\tGO
\n\t<\/font>ALTER DATABASE DBMaint2008 ADD FILE
\n\t   (NAME = N'DataPartition3', FILENAME = N'C:\\SQLskills\\DataPartition3.ndf',
\n\t   SIZE = 10, FILEGROWTH = 10)
\n\tTO FILEGROUP DataPartition3;
\n\tGO<\/font>\n\t<\/p>\n

\n\tCREATE PARTITION FUNCTION Partitions_PFN (INT)
\n\tAS RANGE RIGHT FOR VALUES (1000, 2000);
\n\tGO<\/font>\n\t<\/p>\n

\n\tCREATE PARTITION SCHEME [Partitions_PS]
\n\tAS PARTITION [Partitions_PFN] TO (DataPartition1, DataPartition2, DataPartition3);
\n\tGO<\/font>\n\t<\/p>\n

\n\tCREATE TABLE TestTable (c1 INT IDENTITY, c2 DATETIME DEFAULT GETDATE ());
\n\tCREATE UNIQUE CLUSTERED INDEX TestPK ON TestTable (c1) ON Partitions_PS (c1);
\n\tGO<\/font>\n\t<\/p>\n

\n\tSET NOCOUNT ON;
\n\tGO
\n\tINSERT INTO TestTable DEFAULT VALUES;
\n\tGO 3000<\/font>\n\t<\/p>\n

\n\tDBCC CHECKFILEGROUP (DataPartition1);
\n\tGO<\/font>\n\t<\/p>\n<\/blockquote>\n

\nOn SQL Server 2005 SP3, the output from the final batch contains: <\/font>\n<\/p>\n

\n

\n\tDBCC results for 'TestTable'.
\n\tCannot 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. 
\n\tCannot 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. 
\n\tThere are 999 rows in 3 pages for object "TestTable".
\n\t<\/font>\n\t<\/p>\n<\/blockquote>\n

\nThis shows that it processed the 1000 rows in the first partition, but not the other two – as we'd expect. <\/font>\n<\/p>\n

\nOn SQL Server 2008, the output for TestTable is limited to: <\/font>\n<\/p>\n

\n

\n\tCannot 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.<\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd 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<\/font> will just complete almost instantly. <\/font>\n<\/p>\n

\nAs 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.<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,31,65,86],"tags":[],"class_list":["post-791","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle","category-database-maintenance","category-partitioning","category-sql-server-2008"],"yoast_head":"\nDBCC CHECKFILEGROUP bug on SQL Server 2008 - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DBCC CHECKFILEGROUP bug on SQL Server 2008 - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-09-23T01:54:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:51:41+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/\",\"name\":\"DBCC CHECKFILEGROUP bug on SQL Server 2008 - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-09-23T01:54:00+00:00\",\"dateModified\":\"2017-04-13T16:51:41+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DBCC CHECKFILEGROUP bug on SQL Server 2008\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"DBCC CHECKFILEGROUP bug on SQL Server 2008 - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/","og_locale":"en_US","og_type":"article","og_title":"DBCC CHECKFILEGROUP bug on SQL Server 2008 - Paul S. Randal","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/","og_site_name":"Paul S. Randal","article_published_time":"2009-09-23T01:54:00+00:00","article_modified_time":"2017-04-13T16:51:41+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/","name":"DBCC CHECKFILEGROUP bug on SQL Server 2008 - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-09-23T01:54:00+00:00","dateModified":"2017-04-13T16:51:41+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-checkfilegroup-bug-on-sql-server-2008\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"DBCC CHECKFILEGROUP bug on SQL Server 2008"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/791","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=791"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/791\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=791"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=791"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=791"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}