DBCC CHECKFILEGROUP (T-SQL)

Checks the allocation and structural integrity of all tables (in the current database) in the specified filegroup.

Syntax

DBCC CHECKFILEGROUP
( [{'filegroup' | filegroup_id}] [, NOINDEX]
) [WITH {ALL_ERRORMSGS | NO_INFOMSGS}]

Arguments

'filegroup': Is the name of the filegroup for which to check table allocation and structural integrity. If not specified, the default is the primary filegroup. Filegroup names must conform to the rules for identifiers. For more information, see Using Identifiers.
filegroup_id: Is the filegroup identification number (ID) for which to check table allocation and structural integrity. Obtain filegroup_id from either the FILEGROUP_ID function or the sysfilegroups system table in the database containing the filegroup.
NOINDEX: Specifies that nonclustered indexes for nonsystem tables should not be checked. This decreases execution time. NOINDEX has no effect on system tables. DBCC CHECKFILEGROUP always checks all system table indexes when run on the default filegroup.
WITH: Specifies an option for the number of error messages to be returned. If neither ALL_ERRORMSGS nor NO_INFOMSGS is specified, Microsoft® SQL Server™ returns all error messages.
ALL_ERRORMSGS: Displays all error messages. If not specified, SQL Server displays a maximum of 200 error messages per table. Error messages are sorted by object ID, except for those messages generated from tempdb.
NO_INFOMSGS: Suppresses all informational messages and the report of space used.

Remarks

Executing DBCC CHECKFILEGROUP statements on all filegroups in a database is the same as running a single DBCC CHECKDB statement. The only difference is that any table with indexes on different filegroups has the table and indexes checked multiple times (one time for each filegroup holding the table or any of its indexes).

DBCC CHECKFILEGROUP prevents modification of all tables and indexes in the filegroup (as well as tables in other filegroups whose indexes are in the filegroup currently checked) for the duration of the operation.

During DBCC CHECKFILEGROUP execution, table creation and deletion actions are not allowed.

DBCC CHECKFILEGROUP checks the linkages and sizes of text, ntext, and image pages for each filegroup, and the allocation of all the pages in the filegroup.

For each table in the filegroup, DBCC CHECKFILEGROUP checks that:

Index and data pages are correctly linked.
Indexes are in their proper sorted order.
Pointers are consistent.
The data on each page is reasonable.
Page offsets are reasonable.

If a nonclustered index in the filegroup being explicitly checked is associated with a table in another filegroup, the table in the other filegroup (not originally explicitly checked) is also checked because verifying the index also requires verification of the base table structure. If a table in the filegroup being checked has a nonclustered index in another filegroup, however, the index is not checked because:

The base table structure is not dependent on the structure of a nonclustered index.
The DBCC CHECKFILEGROUP statement is focused on validating only objects in the filegroup. Nonclustered indexes do not have to be scanned to validate the base table.
Only checking the index when the filegroup holding it is specifically checked reduces duplicate processing when DBCC CHECKFILEGROUP is run on multiple filegroups in a database.

It is not possible to have a clustered index and a table on different filegroups, so these considerations only apply to nonclustered indexes.

The references to filegroup and filegroup_id are only relevant in the current database. Be sure to switch context to the proper database before executing DBCC CHECKFILEGROUP. For more information about changing the current database, see USE.

Result Sets

Whether or not any options (except NOINDEX) are specified, DBCC CHECKFILEGROUP returns this result set for the current database, if no database is specified (values may vary):

DBCC results for 'master'.

DBCC results for 'sysobjects'.

There are 862 rows in 13 pages for object 'sysobjects'.

DBCC results for 'sysindexes'.

There are 80 rows in 3 pages for object 'sysindexes'.

DBCC results for 'syscolumns'.

There are 2902 rows in 41 pages for object 'syscolumns'.

DBCC results for 'systypes'.

There are 24 rows in 1 pages for object 'systypes'.

DBCC results for 'syscomments'.

There are 1184 rows in 390 pages for object 'syscomments'.

DBCC results for 'sysfiles1'.

There are 2 rows in 1 pages for object 'sysfiles1'.

DBCC results for 'syspermissions'.

There are 492 rows in 2 pages for object 'syspermissions'.

DBCC results for 'sysusers'.

There are 13 rows in 1 pages for object 'sysusers'.

DBCC results for 'sysdepends'.

There are 3433 rows in 16 pages for object 'sysdepends'.

DBCC results for 'sysreferences'.

There are 0 rows in 1 pages for object 'sysreferences'.

DBCC results for 'sysfulltextcatalogs'.

There are 1 rows in 1 pages for object 'sysfulltextcatalogs'.

DBCC results for 'sysdatabases'.

There are 6 rows in 1 pages for object 'sysdatabases'.

DBCC results for 'sysxlogins'.

There are 3 rows in 1 pages for object 'sysxlogins'.

DBCC results for 'sysdevices'.

There are 6 rows in 1 pages for object 'sysdevices'.

DBCC results for 'sysmessages'.

There are 2768 rows in 109 pages for object 'sysmessages'.

DBCC results for 'sysconfigures'.

There are 41 rows in 1 pages for object 'sysconfigures'.

DBCC results for 'sysservers'.

There are 1 rows in 1 pages for object 'sysservers'.

DBCC results for 'syslanguages'.

There are 23 rows in 2 pages for object 'syslanguages'.

DBCC results for 'syscharsets'.

There are 103 rows in 30 pages for object 'syscharsets'.

DBCC results for 'sysaltfiles'.

There are 2 rows in 1 pages for object 'sysaltfiles'.

DBCC results for 'sysfilegroups'.

There are 1 rows in 1 pages for object 'sysfilegroups'.

DBCC results for 'sysallocations'.

There are 1 rows in 1 pages for object 'sysallocations'.

DBCC results for 'spt_committab'.

There are 0 rows in 1 pages for object 'spt_committab'.

DBCC results for 'spt_monitor'.

There are 1 rows in 1 pages for object 'spt_monitor'.

DBCC results for 'spt_values'.

There are 679 rows in 5 pages for object 'spt_values'.

DBCC results for 'spt_fallback_db'.

There are 0 rows in 1 pages for object 'spt_fallback_db'.

DBCC results for 'spt_fallback_dev'.

There are 0 rows in 1 pages for object 'spt_fallback_dev'.

DBCC results for 'spt_fallback_usg'.

There are 0 rows in 1 pages for object 'spt_fallback_usg'.

DBCC results for 'tbl1'.

There are 5 rows in 1 pages for object 'tbl1'.

DBCC results for 'FulltextTest'.

There are 4 rows in 1 pages for object 'FulltextTest'.

DBCC results for 'MSreplication_options'.

There are 2 rows in 1 pages for object 'MSreplication_options'.

DBCC results for 'spt_datatype_info_ext'.

There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.

DBCC results for 'spt_datatype_info'.

There are 33 rows in 1 pages for object 'spt_datatype_info'.

DBCC results for 'spt_server_info'.

There are 29 rows in 1 pages for object 'spt_server_info'.

DBCC results for 'spt_provider_types'.

There are 23 rows in 1 pages for object 'spt_provider_types'.

CHECKFILEGROUP found 0 allocation errors and 0 consistency errors in database 'master'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC CHECKFILEGROUP returns this result set if the NOINDEX option is specified:

DBCC results for 'pubs'.

Warning: NO_INDEX option of CHECKFILEGROUP being used. Checks on non-system indexes will be skipped.

DBCC results for 'sysobjects'.

There are 89 rows in 1 pages for object 'sysobjects'.

DBCC results for 'sysindexes'.

There are 48 rows in 2 pages for object 'sysindexes'.

DBCC results for 'syscolumns'.

There are 465 rows in 6 pages for object 'syscolumns'.

DBCC results for 'systypes'.

There are 27 rows in 1 pages for object 'systypes'.

DBCC results for 'syscomments'.

There are 129 rows in 9 pages for object 'syscomments'.

DBCC results for 'sysfiles1'.

There are 2 rows in 1 pages for object 'sysfiles1'.

DBCC results for 'syspermissions'.

There are 55 rows in 1 pages for object 'syspermissions'.

DBCC results for 'sysusers'.

There are 13 rows in 1 pages for object 'sysusers'.

DBCC results for 'sysdepends'.

There are 214 rows in 1 pages for object 'sysdepends'.

DBCC results for 'sysreferences'.

There are 10 rows in 1 pages for object 'sysreferences'.

DBCC results for 'sysfulltextcatalogs'.

There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.

DBCC results for 'sysfilegroups'.

There are 1 rows in 1 pages for object 'sysfilegroups'.

DBCC results for 'sysallocations'.

There are 1 rows in 1 pages for object 'sysallocations'.

DBCC results for 'authors'.

There are 23 rows in 1 pages for object 'authors'.

DBCC results for 'publishers'.

There are 8 rows in 1 pages for object 'publishers'.

DBCC results for 'titles'.

There are 18 rows in 1 pages for object 'titles'.

DBCC results for 'titleauthor'.

There are 25 rows in 1 pages for object 'titleauthor'.

DBCC results for 'stores'.

There are 6 rows in 1 pages for object 'stores'.

DBCC results for 'sales'.

There are 21 rows in 1 pages for object 'sales'.

DBCC results for 'roysched'.

There are 86 rows in 1 pages for object 'roysched'.

DBCC results for 'discounts'.

There are 3 rows in 1 pages for object 'discounts'.

DBCC results for 'jobs'.

There are 14 rows in 1 pages for object 'jobs'.

DBCC results for 'pub_info'.

There are 8 rows in 1 pages for object 'pub_info'.

DBCC results for 'employee'.

There are 43 rows in 1 pages for object 'employee'.

CHECKFILEGROUP found 0 allocation errors and 0 consistency errors in database 'pubs'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC CHECKFILEGROUP returns this result set if the NO_INFOMSGS option is specified:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissions

DBCC CHECKFILEGROUP permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, and are not transferable.

Examples

A. Check the PRIMARY filegroup in the pubs database

This example checks the pubs database primary filegroup.

USE pubs

GO

DBCC CHECKFILEGROUP

GO

B. Check the pubs PRIMARY filegroup without nonclustered indexes

This example checks the pubs database primary filegroup (excluding nonclustered indexes) by specifying the primary filegroup’s identification number and by specifying the NOINDEX option.

USE pubs

GO

DBCC CHECKFILEGROUP (1, NOINDEX)

GO

FILEGROUP_ID	sp_helpfilegroup
Physical Database Architecture	sysfilegroups
sp_helpfile	DBCC