Dela via

  • Artikel

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:

  • 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, 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:

  • In-row data

  • LOB data

  • Row-overflow data

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.