| title | sys.allocation_units (Transact-SQL) | Microsoft Docs | ||||
|---|---|---|---|---|---|
| ms.custom | |||||
| ms.date | 06/10/2016 | ||||
| ms.prod | sql | ||||
| ms.prod_service | database-engine, sql-database | ||||
| ms.reviewer | |||||
| ms.technology | system-objects | ||||
| ms.topic | language-reference | ||||
| f1_keywords |
|
||||
| dev_langs |
|
||||
| helpviewer_keywords |
|
||||
| ms.assetid | ec9de780-68fd-4551-b70b-2d3ab3709b3e | ||||
| author | stevestein | ||||
| ms.author | sstein | ||||
| monikerRange | =azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx-md]
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 Note that the 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 [!INCLUDEssDE] 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)