SQL Server - sys.allocation_units

17 December,2012 by Tom Collins

SQL Server  sys.allocation_units contains a row for each particular allocation unit in a  database. There are 3 row types: in-row data pages, row-overflow data pages and LOB data pages. A collection of pages of one type for one partition is  an allocation unit. i.e any partition can have up to 3 rows

 Notes on sys.allocation_units

 1)Sys.allocation_units is not always up to date. For example, if issuing truncate statements or index rebuilds , the adjustment in the sys.allocation_units don’t occur until the transaction is commited. This refers to objects larger than 128 extents.

If you commit a SQL statement such as :

select reservedpages = sum(a.total_pages) from sys.allocation_units a


It will return the total number of pages reserved\allocated , but doesn’t include log pages.

2) The view refers to data pages and does not include log pages. Using the sys.allocation_units for the database size won’t report an accurate figure.

 3) The DMV sys.dm_os_buffer_descriptors   returns information about data pages currently in the Buffer Pool. sys.dm_os_buffer_descriptors   returns a column allocation_unit_id joined back to the sys.allocation_units.

4) DBCC Updateusage Reports and corrects pages and row count inaccuracies in the catalog views. It scans the database data pages and corrects the sys.allocation_units view. 

 Read More

SQL Server Database size Check - SQL Server DBA

SQL Server - Table sizes and percentages - SQL Server DBA

SQL Server - Calculate table size with existing data - SQL Server DBA

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server - sys.allocation_units

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer