Analysis of Allocation Problems

Use sp_iqcheckdb to analyze allocation problems.

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.

Sample of Leaked Space Output

This 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                          |*****
   ...
==============================|==============================|=====

If one or more dbspaces are offline, use the following syntax to show allocation problems for a particular dbspace:

sp_iqcheckdb 'allocation dbspace dbspace-name'

DBCC Allocation Errors

Allocation problems are reported in the output generated by DBCC with sp_iqcheckdb run in a allocation mode or 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.

DBCC Allocation Errors
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 sp_iqcheckdb dropleaks mode 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 sp_iqcheckdb dropleaks mode 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.