dbcc checkalloc ensures that:
All pages are correctly allocated
Partition statistics on the allocation pages are correct
No page is allocated that is not used
All pages are correctly allocated to individual partitions and that no page that is allocated is not used
No page is used that is not allocated
The syntax for dbcc checkalloc is:
dbcc checkalloc [(database_name [, fix | nofix] )]
If you do not provide a database name, dbcc checkalloc checks the current database.
With the fix option, dbcc checkalloc can fix all allocation errors that would otherwise be fixed by dbcc tablealloc and can also fix pages that remain allocated to objects that have been dropped from the database. Before you can use dbcc checkalloc with the fix option, you must put the database into single-user mode. For details on using the fix and no fix options, see “Correcting allocation errors using the fix | nofix option”.
dbcc checkalloc output consists of a block of data for each table, including the system tables and the indexes on each table. For each table or index, it reports the number of pages and extents used. Table information is reported as either INDID=0 or INDID=1. The INDID values are:
Tables without clustered indexes have an INDID=0
For APL tables with clustered indexes, the table data partitions and clustered index partitions are consolidated, with an INDID=1 for the data partitions (or the clustered index partitions).
For DOL tables with clusetered index, the table data partitions have an INDID=0. The clustered index and nonclustered indexes are numbered consecutively, starting with INDID=2.
Partition and page information is listed after PARTITION ID=partition_number.
The following report on pubs2 shows the output for the titleauthor, titles, and stores tables:
*************************************************************** TABLE: titleauthor OBJID = 544001938 PARTITION ID=544001938 FIRST=904 ROOT=920 SORT=1 Data level: indid 1, partition 544001938. 1 Data pages allocated and 2 Extents allocated. Indid : 1, partition : 544001938. 1 Index pages allocated and 2 Extents allocated. PARTITION ID=544001938 FIRST=928 ROOT=928 SORT=0 Indid : 2, partition : 544001938. 1 Index pages allocated and 2 Extents allocated. PARTITION ID=544001938 FIRST=944 ROOT=944 SORT=0 Indid : 3, partition : 544001938. 1 Index pages allocated and 2 Extents allocated. TOTAL # of extents = 8 *************************************************************** TABLE: titles OBJID = 576002052 PARTITION ID=1120003990 FIRST=1282 ROOT=1282 SORT=1 Data level: indid 0, partition 1120003990. 1 Data pages allocated and 1 Extents allocated. PARTITION ID=1136004047 FIRST=1289 ROOT=1289 SORT=1 Data level: indid 0, partition 1136004047. 1 Data pages allocated and 1 Extents allocated. TOTAL # of extents = 2 *************************************************************** TABLE: stores OBJID = 608002166 PARTITION ID=608002166 FIRST=745 ROOT=745 SORT=0 Data level: indid 0, partition 608002166. 1 Data pages allocated and 1 Extents allocated. TOTAL # of extents = 1
