Chapter 6: Creating and Managing User Databases

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 6-2 describes the columns in the report.

**Table 6-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.

Checking summary information for a table

View this book as PDF