DBCC CHECKFILEGROUP
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 ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
}
]
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 (ID) number 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 options on the number of error messages returned, locks obtained, or estimating tempdb requirements. 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.
TABLOCK
Causes DBCC CHECKFILEGROUP to obtain shared table locks.
ESTIMATE ONLY
Displays the estimated amount of tempdb space required to run DBCC CHECKFILEGROUP with all of the other specified options.
Remarks
DBCC CHECKFILEGROUP and DBCC CHECKDB are similar DBCC statements. The main difference lies in the check conducted by DBCC CHECKFILEGROUP: it is limited to the single specified filegroup and required tables.
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 does not acquire table locks by default. Instead, it acquires schema locks that prevent meta data changes but allow changes to the data. The DBCC statement collects information, then scans the log for any additional changes made, merging the two sets of information together to produce a consistent view of the data at the end of the scan.
When the TABLOCK option is specified, DBCC CHECKFILEGROUP acquires shared table locks. This allows more detailed error messages for some classes of errors and minimizes the amount of tempdb space required by avoiding the use of transaction log data.
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.
DBCC CHECKFILEGROUP also performs a physical consistency check on indexed views. The NOINDEX option, used only for backward compatibility, also applies to indexed views.
For each table in the filegroup, DBCC CHECKFILEGROUP checks that:
- Index and data pages are correctly linked.
- Indexes are in their proper sort 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.
By default, DBCC CHECKFILEGROUP performs parallel checking of objects. The degree of parallelism is determined automatically by the query processor. The maximum degree of parallelism is configured in the same manner as that of parallel queries. Use the sp_configure system stored procedure to restrict the maximum number of processors available for DBCC checking. For more information, see max degree of parallelism Option.
Parallel checking can be disabled by using trace flag 2528. For more information, see Trace Flags.
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 '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 NO_INFOMSGS option is specified:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC CHECKFILEGROUP returns this result set when the ESTIMATEONLY option is specified.
Estimated TEMPDB space needed for CHECKALLOC (KB)
-------------------------------------------------
15
(1 row(s) affected)
Estimated TEMPDB space needed for CHECKTABLES (KB)
--------------------------------------------------
207
(1 row(s) affected)
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 identification number of the primary filegroup, and by specifying the NOINDEX option.
USE pubs
GO
DBCC CHECKFILEGROUP (1, NOINDEX)
GO
See Also
Features Supported by the Editions of SQL Server 2000
How to configure the number of processors available for parallel queries (Enterprise Manager)