sp_spaceused System Stored Procedure

Displays the number of rows, the disk space reserved, and the disk space used by a table in a database, or displaces the disk space reserved and used by an entire database.

Syntax

sp_spaceused [objname] [[,] @updateusage = {true | false}]

where

objname

Specifies the name of the table for which space usage information (reserved and allocated space) is requested.

@updateusage

Specifies whether or not DBCC UPDATEUSAGE should be run within the database (when no objname is specified) or on a specific object (when objname is specified). Setting this option to true could take a significant amount of time. If preferred, DBCC UPDATEUSAGE can be run separately. See the remarks for more information.

Remarks

Executing sp_spaceused computes the amount of disk space used for data and indexes, and the disk space used by a table in the current database. If no table (objname) is given, sp_spaceused reports on the space used by the entire current database.

After you drop an index, sp_spaceused reports inaccurate information. This is a known limitation. However, sp_spaceused provides a quick, rough estimate. If you notice incorrect values when you execute sp_spaceused, run DBCC UPDATEUSAGE to correct the inaccuracies. Because this statement takes some time to run on large tables or databases use it only when you suspect incorrect values being returned or when other users are not active.

Examples

A.    Space Information About a Table

This example reports on the amount of space allocated (reserved) for the titles table, the amount used for data, the amount used for index(es), and the unused space reserved by database objects.

sp_spaceused titles
go

name    rows  reserved  data   index_size   unused
------- ----- --------- ------ ------------ ------------- 
titles  18    48 KB     6 KB   8 KB         34 KB 

B.    Updated Space Information About a Complete Database

This example shows a summary of space used in the current database with the optional @updateusage.

sp_spaceused @updateusage = 'TRUE'
go

DBCC UPDATEUSAGE: messages are printed...
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
 
database_name               database_size   unallocated space  
--------------------------- --------------- ------------------ 
pubs                        3.00 MB         1.69 MB            
 
reserved       data          index_size     unused          
-------------- ------------- -------------- --------------- 
1344 KB        300 KB        116 KB         928 KB          

Permission

Execute permission defaults to the public group.

Tables Used

master..sysusages, sysindexes, sysobjects

See Also