DBCC CHECKALLOC
Checks the consistency of disk space allocation structures for a specified database.
Syntax
DBCC CHECKALLOC
( 'database_name'
[ , NOINDEX
|
{ REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
}
]
Arguments
'database_name'
Is the database for which to check allocation and page usage. If not specified, the default is the current database. Database names must conform to the rules for identifiers. For more information, see Using Identifiers.
NOINDEX
Specifies that nonclustered indexes for nonsystem tables should not be checked.
Note NOINDEX is maintained for backward compatibility only. All indexes are checked when executing DBCC CHECKALLOC.
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Specifies that DBCC CHECKALLOC repair the found errors. The given database_name must be in single-user mode to use one of these repair options, and can be one of the following.
| Value | Description |
|---|---|
| REPAIR_ALLOW_DATA_LOSS | Performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can result in some data loss. The repair can be done under a user transaction to allow the user to roll back the changes made. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped. After repairs are completed, back up the database. |
| REPAIR_FAST | Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss. |
| REPAIR_REBUILD | Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss. |
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™ 2000 returns all error messages.
ALL_ERRORMSGS
Displays all error messages. If not specified, SQL Server displays a maximum of 200 error messages per object.
NO_INFOMSGS
Suppresses all informational messages and the report of space used.
TABLOCK
Causes DBCC command to obtain shared table locks. Ignored for DBCC CHECKALLOC.
ESTIMATE ONLY
Displays the estimated amount of tempdb space required to run DBCC CHECKALLOC with all of the other specified options.
Remarks
DBCC CHECKALLOC checks allocation and page usage in a database, including indexed views. The NOINDEX option, used only for backward compatibility, also applies to indexed views.
It is not necessary to execute DBCC CHECKALLOC if DBCC CHECKDB has already been executed. DBCC CHECKDB is a superset of DBCC CHECKALLOC and includes allocation checks in addition to checks of index structure and data integrity.
DBCC CHECKDB is the safest repair statement because it identifies and repairs the widest possible range of errors. If only allocation errors are reported for a database, execute DBCC CHECKALLOC with a repair option to correct them. However, to ensure that all errors (including allocation errors) are repaired properly, execute DBCC CHECKDB with a repair option. DBCC CHECKALLOC messages are sorted by object ID, except for those messages generated from tempdb. DBCC CHECKALLOC validates the allocation of all data pages in the database while DBCC CHECKDB validates the page information used in the storage of data in addition to validating the allocation information.
DBCC CHECKALLOC does not acquire table locks by default. Instead, it acquires schema locks that prevent meta data changes but allow changes to the data while the DBCC CHECKALLOC is in progress. The DBCC statement collects information, and 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.
Result Sets
This table describes the information DBCC CHECKALLOC returns.
| Item | Description |
|---|---|
| FirstIAM | Internal use only. |
| Root | Internal use only. |
| Dpages | Data page count from sysindexes. |
| Pages used | Allocated pages. |
| Dedicated extents | Extents allocated to the object.
If mixed allocation pages are used, there may be pages allocated with no extents. |
The second part of a DBCC CHECKALLOC report is an allocation summary for each index in each file. This summary gives users an idea of the distribution of the data.
| Item | Description |
|---|---|
| Reserved | Pages allocated to the index and the unused pages in allocated extents. |
| Used | Pages allocated and in use by the index. |
Whether or not any options (except WITH NO_INFOMSGS) are specified, DBCC CHECKALLOC returns this result set (values may vary):
DBCC results for 'master'.
***************************************************************
Table sysobjects Object ID 1.
Index ID 1 FirstIAM (1:11) Root (1:12) Dpages 22.
Index ID 1. 24 pages used in 5 dedicated extents.
Index ID 2 FirstIAM (1:1368) Root (1:1362) Dpages 10.
Index ID 2. 12 pages used in 2 dedicated extents.
Index ID 3 FirstIAM (1:1392) Root (1:1408) Dpages 4.
Index ID 3. 6 pages used in 0 dedicated extents.
Total number of extents is 7.
***************************************************************
'...'
***************************************************************
Table spt_server_info Object ID 1938105945.
Index ID 1 FirstIAM (1:520) Root (1:508) Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Processed 52 entries in sysindexes for database ID 1.
File 1. Number of extents = 210, used pages = 1126, reserved pages = 1280.
File 1 (number of mixed extents = 73, mixed pages = 184).
Object ID 1, Index ID 0, data extents 5, pages 24, mixed extent pages 9.
'...'
Object ID 1938105945, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Total number of extents = 210, used pages = 1126, reserved pages = 1280 in this database.
(number of mixed extents = 73, mixed pages = 184) in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'master'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC CHECKALLOC returns this result set when the ESTIMATE ONLY option is specified.
Estimated TEMPDB space needed for CHECKALLOC (KB)
-------------------------------------------------
34
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Permissions
DBCC CHECKALLOC permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, and are not transferable.
Examples
This example executes DBCC CHECKALLOC for the current database and for the pubs database.
-- Check the current database.
DBCC CHECKALLOC
GO
-- Check the pubs database.
DBCC CHECKALLOC ('pubs')
GO