Checking space used in a database

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.

Table 21-2: Columns in sp_spaceused output

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.