DBCC CHECKALLOC (T-SQL)

Checks the allocation and use of all pages in the specified database.

Syntax

DBCC CHECKALLOC
    (    'database_name'
            [,     NOINDEX
                |
                {    REPAIR_ALLOW_DATA_LOSS
                    | REPAIR_FAST
                    | REPAIR_REBUILD
                }]
    ) [WITH {ALL_ERRORMSGS | NO_INFOMSGS}]

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 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 object.

NO_INFOMSGS

Suppresses all informational messages and the report of space used.

Remarks

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 catches 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 acquires a schema lock to prevent schema modifications while the DBCC CHECKALLOC is in progress. DBCC CHECKALLOC may report errors for tempdb work tables that are created or dropped for user queries while the DBCC CHECKALLOC is running; these can be ignored.

Result Sets

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:4) Root (1:6) Dpages 13 Sort 0.

Data level 1. 15 data pages in 1 extents.

Index ID 2 FirstIAM (1:841) Root (1:856) Dpages 8 Sort 0.

Index ID 2. 10 index pages in 2 extents.

Index ID 3 FirstIAM (1:844) Root (1:846) Dpages 3 Sort 0.

Index ID 3. 5 index pages in 0 extents.

Total number of extents is 3.

***************************************************************

Table sysindexes Object ID 2.

Index ID 1 FirstIAM (1:25) Root (1:26) Dpages 3 Sort 0.

Data level 1. 5 data pages in 0 extents.

Index ID 255 FirstIAM (1:35) Root (1:16) Dpages 0 Sort 0.

Index ID 255. 5 index pages in 1 extents.

Total number of extents is 1.

***************************************************************

Table syscolumns Object ID 3.

Index ID 1 FirstIAM (1:30) Root (1:31) Dpages 41 Sort 0.

Data level 1. 43 data pages in 5 extents.

Index ID 2 FirstIAM (1:847) Root (1:888) Dpages 17 Sort 0.

Index ID 2. 19 index pages in 3 extents.

Total number of extents is 8.

***************************************************************

Table systypes Object ID 4.

Index ID 1 FirstIAM (1:913) Root (1:914) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Index ID 2 FirstIAM (1:916) Root (1:915) Dpages 1 Sort 0.

Index ID 2. 2 index pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table syscomments Object ID 6.

Index ID 1 FirstIAM (1:272) Root (1:273) Dpages 390 Sort 0.

Data level 1. 394 data pages in 50 extents.

Total number of extents is 50.

***************************************************************

Table sysfiles1 Object ID 8.

Index ID 0 FirstIAM (1:7) Root (1:32) Dpages 2 Sort 0.

Data level 1. 2 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table syspermissions Object ID 9.

Index ID 1 FirstIAM (1:275) Root (1:276) Dpages 2 Sort 0.

Data level 1. 4 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table sysusers Object ID 10.

Index ID 1 FirstIAM (1:278) Root (1:279) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Index ID 2 FirstIAM (1:918) Root (1:917) Dpages 1 Sort 0.

Index ID 2. 2 index pages in 0 extents.

Index ID 3 FirstIAM (1:281) Root (1:280) Dpages 1 Sort 0.

Index ID 3. 2 index pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table sysdepends Object ID 12.

Index ID 1 FirstIAM (1:286) Root (1:287) Dpages 16 Sort 0.

Data level 1. 18 data pages in 2 extents.

Total number of extents is 2.

***************************************************************

Table sysreferences Object ID 14.

Index ID 1 FirstIAM (1:289) Root (1:290) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Index ID 2 FirstIAM (1:284) Root (1:283) Dpages 1 Sort 0.

Index ID 2. 2 index pages in 0 extents.

Index ID 3 FirstIAM (1:292) Root (1:291) Dpages 1 Sort 0.

Index ID 3. 2 index pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table sysfulltextcatalogs Object ID 19.

Index ID 1 FirstIAM (1:296) Root (1:297) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Index ID 2 FirstIAM (1:920) Root (1:919) Dpages 1 Sort 0.

Index ID 2. 2 index pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table sysdatabases Object ID 30.

Index ID 1 FirstIAM (1:605) Root (1:606) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Index ID 2 FirstIAM (1:840) Root (1:607) Dpages 1 Sort 0.

Index ID 2. 2 index pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table sysxlogins Object ID 33.

Index ID 1 FirstIAM (1:922) Root (1:923) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Index ID 2 FirstIAM (1:925) Root (1:924) Dpages 1 Sort 0.

Index ID 2. 2 index pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table sysdevices Object ID 35.

Index ID 1 FirstIAM (1:927) Root (1:928) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table sysmessages Object ID 36.

Index ID 1 FirstIAM (1:309) Root (1:310) Dpages 109 Sort 0.

Data level 1. 111 data pages in 13 extents.

Total number of extents is 13.

***************************************************************

Table sysconfigures Object ID 37.

Index ID 1 FirstIAM (1:312) Root (1:313) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table sysservers Object ID 40.

Index ID 1 FirstIAM (1:315) Root (1:316) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Index ID 2 FirstIAM (1:930) Root (1:929) Dpages 1 Sort 0.

Index ID 2. 2 index pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table syslanguages Object ID 44.

Index ID 1 FirstIAM (1:320) Root (1:321) Dpages 2 Sort 0.

Data level 1. 4 data pages in 0 extents.

Index ID 2 FirstIAM (1:932) Root (1:931) Dpages 1 Sort 0.

Index ID 2. 2 index pages in 0 extents.

Index ID 3 FirstIAM (1:934) Root (1:933) Dpages 1 Sort 0.

Index ID 3. 2 index pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table syscharsets Object ID 45.

Index ID 1 FirstIAM (1:328) Root (1:329) Dpages 30 Sort 0.

Data level 1. 32 data pages in 3 extents.

Index ID 2 FirstIAM (1:936) Root (1:935) Dpages 1 Sort 0.

Index ID 2. 2 index pages in 0 extents.

Total number of extents is 3.

***************************************************************

Table sysaltfiles Object ID 94.

Index ID 1 FirstIAM (1:13) Root (1:14) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table sysfilegroups Object ID 96.

Index ID 1 FirstIAM (1:267) Root (1:268) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Index ID 2 FirstIAM (1:938) Root (1:937) Dpages 1 Sort 0.

Index ID 2. 2 index pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table sysallocations Object ID 98.

Index ID 1 FirstIAM (1:33) Root (1:34) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table spt_committab Object ID 85575343.

Index ID 1 FirstIAM (1:359) Root (1:360) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table spt_monitor Object ID 101575400.

Index ID 0 FirstIAM (1:357) Root (1:356) Dpages 1 Sort 0.

Data level 1. 2 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table spt_values Object ID 117575457.

Index ID 1 FirstIAM (1:939) Root (1:952) Dpages 5 Sort 0.

Data level 1. 7 data pages in 2 extents.

Index ID 2 FirstIAM (1:941) Root (1:943) Dpages 4 Sort 0.

Index ID 2. 6 index pages in 0 extents.

Total number of extents is 2.

***************************************************************

Table spt_fallback_db Object ID 149575571.

Index ID 0 FirstIAM (1:368) Root (1:367) Dpages 1 Sort 0.

Data level 1. 2 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table spt_fallback_dev Object ID 165575628.

Index ID 0 FirstIAM (1:370) Root (1:369) Dpages 1 Sort 0.

Data level 1. 2 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table spt_fallback_usg Object ID 181575685.

Index ID 0 FirstIAM (1:372) Root (1:371) Dpages 1 Sort 0.

Data level 1. 2 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table tbl1 Object ID 672721449.

Index ID 0 FirstIAM (1:27) Root (1:11) Dpages 1 Sort 0.

Data level 1. 2 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table FulltextTest Object ID 912722304.

Index ID 1 FirstIAM (1:269) Root (1:293) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table MSreplication_options Object ID 1163151189.

Index ID 0 FirstIAM (1:461) Root (1:460) Dpages 1 Sort 0.

Data level 1. 2 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table spt_datatype_info_ext Object ID 1410104064.

Index ID 1 FirstIAM (1:406) Root (1:407) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table spt_datatype_info Object ID 1426104121.

Index ID 1 FirstIAM (1:417) Root (1:418) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table spt_server_info Object ID 1442104178.

Index ID 1 FirstIAM (1:420) Root (1:421) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Table spt_provider_types Object ID 2034106287.

Index ID 1 FirstIAM (1:456) Root (1:457) Dpages 1 Sort 0.

Data level 1. 3 data pages in 0 extents.

Total number of extents is 0.

***************************************************************

Processed 53 entries in sysindexes for database ID 1.

Allocation page (1:2). Number of extents = 113, used pages = 778, referenced pages = 719.

(1:2) (number of mixed extents = 31, mixed pages = 185).

Object ID 1, Index ID 1, data extents 1, pages 15, mixed extent pages 9.

Object ID 1, Index ID 2, index extents 2, pages 10, mixed extent pages 3.

Object ID 1, Index ID 3, index extents 0, pages 5, mixed extent pages 5.

Object ID 2, Index ID 1, data extents 0, pages 5, mixed extent pages 5.

Object ID 2, Index ID 255, index extents 1, pages 5, mixed extent pages 4.

Object ID 3, Index ID 1, data extents 5, pages 43, mixed extent pages 9.

Object ID 3, Index ID 2, index extents 3, pages 19, mixed extent pages 3.

Object ID 4, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 4, Index ID 2, index extents 0, pages 2, mixed extent pages 2.

Object ID 6, Index ID 1, data extents 50, pages 394, mixed extent pages 9.

Object ID 8, Index ID 0, data extents 0, pages 2, mixed extent pages 2.

Object ID 9, Index ID 1, data extents 0, pages 4, mixed extent pages 4.

Object ID 10, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 10, Index ID 2, index extents 0, pages 2, mixed extent pages 2.

Object ID 10, Index ID 3, index extents 0, pages 2, mixed extent pages 2.

Object ID 12, Index ID 1, data extents 2, pages 18, mixed extent pages 9.

Object ID 14, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 14, Index ID 2, index extents 0, pages 2, mixed extent pages 2.

Object ID 14, Index ID 3, index extents 0, pages 2, mixed extent pages 2.

Object ID 19, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 19, Index ID 2, index extents 0, pages 2, mixed extent pages 2.

Object ID 30, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 30, Index ID 2, index extents 0, pages 2, mixed extent pages 2.

Object ID 33, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 33, Index ID 2, index extents 0, pages 2, mixed extent pages 2.

Object ID 35, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 36, Index ID 1, data extents 13, pages 111, mixed extent pages 9.

Object ID 37, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 40, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 40, Index ID 2, index extents 0, pages 2, mixed extent pages 2.

Object ID 44, Index ID 1, data extents 0, pages 4, mixed extent pages 4.

Object ID 44, Index ID 2, index extents 0, pages 2, mixed extent pages 2.

Object ID 44, Index ID 3, index extents 0, pages 2, mixed extent pages 2.

Object ID 45, Index ID 1, data extents 3, pages 32, mixed extent pages 9.

Object ID 45, Index ID 2, index extents 0, pages 2, mixed extent pages 2.

Object ID 94, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 96, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 96, Index ID 2, index extents 0, pages 2, mixed extent pages 2.

Object ID 98, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 85575343, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 101575400, Index ID 0, data extents 0, pages 2, mixed extent pages 2.

Object ID 117575457, Index ID 1, data extents 2, pages 7, mixed extent pages 1.

Object ID 117575457, Index ID 2, index extents 0, pages 6, mixed extent pages 6.

Object ID 149575571, Index ID 0, data extents 0, pages 2, mixed extent pages 2.

Object ID 165575628, Index ID 0, data extents 0, pages 2, mixed extent pages 2.

Object ID 181575685, Index ID 0, data extents 0, pages 2, mixed extent pages 2.

Object ID 672721449, Index ID 0, data extents 0, pages 2, mixed extent pages 2.

Object ID 912722304, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 1163151189, Index ID 0, data extents 0, pages 2, mixed extent pages 2.

Object ID 1410104064, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 1426104121, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 1442104178, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 2034106287, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Total number of extents = 113, used pages = 778, referenced pages = 719 in this database.

(number of mixed extents = 31, mixed pages = 185) 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.

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

DBCC NEWALLOC	Space Allocation and Reuse
sp_dboption	DBCC