sys.allocation_units (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Contains a row for each allocation unit in the database.

Column name	Data type	Description
allocation_unit_id	bigint	ID of the allocation unit. Is unique within a database.
type	tinyint	Type of allocation unit: 0 = Dropped 1 = In-row data (all data types, except LOB data types) 2 = Large object (LOB) data (text, ntext, image, xml, large value types, and CLR user-defined types) 3 = Row-overflow data
type_desc	nvarchar(60)	Description of the allocation unit type: DROPPED IN_ROW_DATA LOB_DATA ROW_OVERFLOW_DATA
container_id	bigint	ID of the storage container associated with the allocation unit. If type = 1 or 3 in a rowstore index container_id = sys.partitions.hobt_id. If type = 1 or 3 in a columnstore index, container_id = sys.column_store_row_groups.delta_store_hobt_id. If type is 2, then container_id = sys.partitions.partition_id. 0 = Allocation unit marked for deferred drop
data_space_id	int	ID of the filegroup in which this allocation unit resides.
total_pages	bigint	Total number of pages allocated or reserved by this allocation unit.
used_pages	bigint	Number of total pages actually in use.
data_pages	bigint	Number of used pages that have: In-row data LOB data Row-overflow data Note that the value returned excludes internal index pages and allocation-management pages.

Note

When you drop or rebuild large indexes, drop large tables, or truncate large tables or partitions, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.allocation_units immediately after dropping or truncating a large object may not reflect the actual disk space available.

When Accelerated Database Recovery is enabled, deferred drop is used regardless of object size.

Permissions

Requires membership in the public role. For more information, see Metadata Visibility Configuration.

Examples

Determine space used by object and type of an allocation unit

The following query returns all the user tables in a database and the amount of space used in each, by allocation unit type.

SELECT t.object_id AS ObjectID,
       OBJECT_NAME(t.object_id) AS ObjectName,
       SUM(u.total_pages) * 8 AS Total_Reserved_kb,
       SUM(u.used_pages) * 8 AS Used_Space_kb,
       u.type_desc AS TypeDesc,
       MAX(p.rows) AS RowsCount
FROM sys.allocation_units AS u
JOIN sys.partitions AS p ON u.container_id = p.hobt_id
JOIN sys.tables AS t ON p.object_id = t.object_id
GROUP BY t.object_id,
         OBJECT_NAME(t.object_id),
         u.type_desc
ORDER BY Used_Space_kb DESC,
         ObjectName;

See Also

sys.partitions (Transact-SQL)
Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)