sys.allocation_units (Transact-SQL)
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:
|
container_id |
bigint |
ID of the storage container associated with the allocation unit. If type = 1 or 3, container_id = sys.partitions.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:
Value returned excludes internal index pages and allocation-management pages. |
Note
When you drop or rebuild large indexes, or drop or truncate large tables, 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. For more information about deferred allocations, see Dropping and Rebuilding Large Objects.
Permissions
Requires membership in the public role. For more information, see Metadata Visibility Configuration.
See Also