To get a summary of the amount of storage space used by a database, execute sp_spaceused in the database:
sp_spaceused
database_name database_size ------------------------------ ------------- pubs2 2.0 MB reserved data index_size unused ------------- ------------- --------------- -------- 1720 KB 536 KB 344 KB 840 KB
Table 21-2 describes the columns in the report.
Column |
Description |
|---|---|
database_name |
The name of the database being examined. |
database_size |
The amount of space allocated to the database by create database or alter database. |
reserved |
The amount of space that has been allocated to all the tables and indexes created in the database. (Space is allocated to database objects inside a database in increments of 1 extent, or 8 pages, at a time.) |
data, index_size |
The amount of space used by data and indexes. |
unused |
The amount of space that has been reserved but not yet used by existing tables and indexes. |
The sum of the values in the unused, index_size, and data columns should equal the figure in the reserved column. Subtract reserved from database_size to get the amount of unreserved space. This space is available for new or existing objects that grow beyond the space that has been reserved for them.
By running sp_spaceused regularly, you can monitor the amount of database space available. For example, if the reserved value is close to the database_size value, you are running out of space for new objects. If the unused value is also small, you are running out of space for additional data as well.
