Disk Space Usage : unallocated, unused and reserved

Sql server 提供了一些standard reports，可以查看Disk Usage，Index Usage 和 Top transactions

查看 Database的Disk Space Usage Report，截图如下

sanpshot中，Data Files Space usage分为4部分：Index ， Data，Unallocated 和 Unused。

• Index 是Index objects实际占用的space
• Data 是table objects实际占用的space
• Unused 是已经allocate 给db objects(index or table)，但是objects未实际占用的space
• Reserved 是table objects实际占用的space +Index Objects所占用的space + Unused space，可以理解为已经 allocate 给数据库objects使用的space。
• Unallocated是没有allocate给任何db object的space，这部分space不属于任意object，没有被任何数据库object使用。如果有object需要space，可以从这部分space中allocate。
• database file的total size = Reserved Space+Unallocated Space。

Unallocated space的存在是预留存储空间，在DB运行时，如果有 object 需要空间，那么Sql Server直接将这部分space分配给object，而不用向OS申请额外的disk space。向OS申请额外的disk space会导致data file的grow，由于申请额外的disk space需要耗费一定的时间对space进行初始化，可能会导致transaction timeout，导致transaction失败而rollback。所以，良好的DB Design都会预留足够的disk space，即保持一定量的Unallocated space。

如果Unallocated Space 过大，会导致Disk space exhaust，这时就需要shrink file，以free space。命令dbcc shrinkfile 释放的空间就是Unallocated space，释放之后，这部分空间返回给OS。

Unallocated is extents that are not in use. In essence, these extents are maked with bit 1 in GAM page. Any type of grow will produce unallocated extents.

Unused are pages on allocated extents, but thise pages are not yet used by any objects. As soon as an extent is allocated (either as uniform or shared extent), we have 8 reserved pages on that extent. Some pages are used, and some are unused.

图片来源于：SQL Server – Understanding ‘sp_spaceused’ Results for Database Size Information

推荐文档：Difference between unallocated and unused space

Unallocated space could be returned to the OS when you shrink a file, but the unused space is space that has already been allocated to an object, but just doesn’t yet have any data stored in it.

Unallocated space is the sum of the space used by extents that are not yet allocated to any database objects. So basically it’s the size of the database file(s) minus the sum of the space used by all extents that are assigned to objects be they data,index,GAM,IAM,SGAM,PFS,BCM or DCM pages.

Unused space is the sum of free space available on pages in the data file(s) that have been allocated to an object or objects. Thus running sp_spaceused on a table for instance, the sum of the data and index page space plus the unused space will give you the total space (reserved column in the resultset).

As rows are inserted,updated and deleted in a table then in almost all circumstances there will be some free space on each page. As deletes and updates occur, plus inserts into a table with a clustered index where inserts may not be in clustered index order, then there may well be readjustment of space on the pages e.g. page splits, ghost records etc. Some of this space may be reused but some of it may not, thus as there is transactional activity in your database , the unused space will tend to increase along with the data and index space. This is where defragmenting your indexes can come into play in terms of compacting this unused space and making your data pages and indexes more efficent. You can determine the average freespace on a page for an object using dbcc showcontig.

It is not uncommon to have some 30-50% of the database size as unallocated.

…at database level :

USE AdventureWorks2008R2
GO

EXEC sp_spaceused
GO

Results

First Recordset:

• database_name: Name of the current database
• database_size: Size of the current database in megabytes. database_size includes both data and log files
• unallocated space: Space in the database that has not been reserved for database objects

Second Recordset:

• reserved: Total amount of space allocated by objects in the database
• data: Total amount of space used by data
• index_size: Total amount of space used by indexes
• unused: Total amount of space reserved for objects in the database, but not yet used

…at table level:

USE AdventureWorks2008R2
GO

EXEC sp_spaceused N‘Person.Person‘
GO

Results

• name: Table name for which space usage information was requested
• rows: Number of rows existing in the table
• reserved: Total amount of reserved space for table data and indexes
• data: Amount of space used by table data
• index_size: Amount of space used by table indexes
• unused: Total amount of space reserved for table but no yet used

Disk Space Usage : unallocated, unused and reserved

原文：http://www.cnblogs.com/ljhdo/p/5128259.html