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)