DBCC CheckFilegroup

Being day five of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CHECKFILEGROUP.

Description:

DBCC CHECKFILEGROUP is used for a specific filegroup to check the disk allocation and structural integrity of all tables and indexed views.

DBCC CHECKFILEGROUP Syntax:

dbcc checkfilegroup
(
    [ { 'filegroup_name' | filegroup_id | 0 } ]
    [ , NOINDEX ]
)
    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ , [ NO_INFOMSGS ] ]
            [ , [ TABLOCK ] ]
            [ , [ ESTIMATEONLY ] ]
        }
    ]

filegroup_name – The name of the filegroup to be checked. Ddefault (or if 0 is specified) = the primary filegroup.
NOINDEX – Skip intensive checks of nonclustered indexes.
ALL_ERRORMSGS – Return all reported errors per object, default = first 200 errors.
TABLOCK – Obtain locks instead of using an internal database snapshot.
ESTIMATEONLY – Display the estimated amount of tempdb space that would be required.
PHYSICAL_ONLY – Limit checking to the integrity of the physical structure of the page, record headers and the physical structure of B-trees.

DBCC CHECKFILEGROUP performs the following commands:

DBCC CHECKALLOC of the filegroup.
DBCC CHECKTABLE of every table and indexed view in the filegroup.

Example:

Before using DBCC CheckFileGroup, you need to know what filegroups you are using. The following query shows how to list your filegroups for a specific database using T-SQL. The following filegroup check is being done for the northwind database.

USE northwind;
GO

SELECT * FROM  sys.filegroups;

Now the filegroup name, or filegroup id is available it can be used to check the filegroup with DBCC as shown in the following example.

dbcc checkfilegroup ('PRIMARY');

Which shows the following output on success:
DBCC results for ‘Northwind’.
DBCC results for ‘sys.sysrscols’.
There are 1024 rows in 12 pages for object “sys.sysrscols”.
DBCC results for ‘sys.sysrowsets’.
There are 168 rows in 3 pages for object “sys.sysrowsets”.
DBCC results for ‘sys.sysclones’.
There are 0 rows in 0 pages for object “sys.sysclones”.
DBCC results for ‘sys.sysallocunits’.
There are 186 rows in 2 pages for object “sys.sysallocunits”.
DBCC results for ‘sys.sysfiles1’.
There are 2 rows in 1 pages for object “sys.sysfiles1”.
DBCC results for ‘sys.sysseobjvalues’.
.
.
.
.
DBCC results for ‘Customers’.
There are 91 rows in 3 pages for object “Customers”.
DBCC results for ‘Shippers’.
There are 3 rows in 1 pages for object “Shippers”.
DBCC results for ‘Suppliers’.
There are 29 rows in 1 pages for object “Suppliers”.
CHECKFILEGROUP found 0 allocation errors and 0 consistency errors in database ‘Northwind’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If you want to reduce the output and only see errors you can run DBCC CheckFilegroup with the NO_INFOMSGS option for instance:

dbcc checkfilegroup ('PRIMARY') WITH NO_INFOMSGS ;

Which on success only displays Command(s) completed successfully.

BUG In SQL Server 2008:

There was a bug with DBCC CheckFilegroup in the initial release of SQL Server 2008. This bug was fixed in CU8 or sp2. The bug does not exist in SQL Server 2008 R2 or newer.

Notes:

For more information see TSQL Wiki DBCC checkfilegroup.

DBCC Command month at SteveStedman.com is almost as much fun as a slinky on an escalator.