This section describes how to analyze allocation problems using sp_iqcheckdb, shows the DBCC output when allocation problems are detected, and describes the DBCC errors related to allocation problems.
The -iqdroplks server option is used in conjunction with sp_iqcheckdb to repair allocation problems. If the server is started with this option and sp_iqcheckdb 'allocation database' is executed, then allocation errors are repaired when DBCC completes. Although these changes are applied immediately, they are not persistent until a checkpoint completes.
The database maintains an allocation map, also known as a free list, which tracks the blocks that are in use by database objects. DBCC detects three types of allocation problems:
leaked blocks A leaked block is a block that is allocated according to the database allocation map, but is found not to be part of any database objects. DBCC can recover leaked blocks.
unallocated blocks An unallocated block is a block that is not allocated according to the database allocation map, but is found to be in use by a database object. DBCC can recover unallocated blocks.
multiply owned blocks A multiply owned block is a block that is in use by more than one database object. At least one of the structures involved contains inconsistent data. DBCC cannot repair this type of allocation problem. If you encounter this type of error, run DBCC again, specifying a list of indexes, until you identify the indexes that share the block. These indexes must then all be dropped to eliminate the multiply owned block. See the section “Dropping inconsistent indexes, tables, or columns” for more information on dropping inconsistent indexes.
The following is an example of the output you see when you run sp_iqcheckdb and there is leaked space. Lines with asterisks (*****) contain information about allocation problems. In this example, DBCC reports 16 leaked blocks.
The command line executed for this example is sp_iqcheckdb
'allocation database'.
Stat Value Flags ==============================|==============================|===== DBCC Allocation Mode Report | | ==============================|==============================|===== ** DBCC Status |Errors Detected |***** DBCC Work units Dispatched |164 | DBCC Work units Completed |164 | | | ==============================|==============================|===== Allocation Summary | | ==============================|==============================|===== Blocks Total |8192 | Blocks in Current Version |4785 | Blocks in All Versions |4785 | Blocks in Use |4801 | % Blocks in Use |58 | ** Blocks Leaked |16 |***** | | ==============================|==============================|===== Allocation Statistics | | ==============================|==============================|===== ... ** 1st Unowned PBN |1994 |***** ... ==============================|==============================|=====
Allocation problems are reported in the output generated by DBCC with sp_iqcheckdb run in a verification mode. If the Allocation Summary section has values flagged with asterisks, such as “** Blocks Leaked” or “** Blocks with Multiple Owners,” then there are allocation problems.
Messages in the DBCC output related to allocation problems are listed in the following table. See the section “DBCC error messages” for a more extensive list of DBCC messages.
DBCC message |
Description/action |
|---|---|
Block Count Mismatch |
This count always accompanies other allocation errors. |
Blocks Leaked 1st Unowned PBN |
Blocks that were found not to be in use by any database object. Use -iqdroplks to repair. |
Blocks with Multiple Owners 1st Multiple Owner PBN |
Blocks in use by more than one database object. Drop the object that is reported as inconsistent. |
Unallocated Blocks in Use 1st Unallocated PBN |
Blocks in use by a database object, but not marked as in use. Use -iqdroplks to repair. |
If the Allocation Summary lines indicate no problem, but the Index Summary section reports a value for “Inconsistent Index Count,” then this indicates one or more inconsistent indexes. See the section “Repairing index errors” for information on repairing indexes.
