sys.allocation_units (Transact-SQL)


Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Contains a row for each allocation unit in the database.

Applies to: SQL Server (SQL Server 2008 through current version).
Column nameData typeDescription
allocation_unit_idbigintID of the allocation unit. Is unique within a database.
typetinyintType 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_descnvarchar(60)Description of the allocation unit type:




container_idbigintID 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_idintID of the filegroup in which this allocation unit resides.
total_pagesbigintTotal number of pages allocated or reserved by this allocation unit.
used_pagesbigintNumber of total pages actually in use.
data_pagesbigintNumber 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.
System_CAPS_ICON_note.jpg 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.

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

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

Community Additions