TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sp_spaceused (Transact-SQL)

 

Updated: September 12, 2015

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

Topic link icon Transact-SQL Syntax Conventions

  
sp_spaceused [[ @objname = ] 'objname' ]   
[, [ @updateusage = ] 'updateusage' ]  
[, [ @mode = ] 'mode' ]  
[, [ @oneresultset = ] oneresultset ]  

[ @objname=] 'objname'
Is the qualified or nonqualified name of the table, indexed view, or queue for which space usage information is requested. Quotation marks are required only if a qualified object name is specified. If a fully qualified object name (including a database name) is provided, the database name must be the name of the current database.

If objname is not specified, results are returned for the whole database.

objname is nvarchar(776), with a default of NULL.

[ @updateusage=] 'updateusage'
Indicates DBCC UPDATEUSAGE should be run to update space usage information. When objname is not specified, the statement is run on the whole database; otherwise, the statement is run on objname. Values can be true or false. updateusage is varchar(5), with a default of false.

[ @mode=] 'mode'
Indicates the scope of the results. For a stretched table or database, the mode parameter lets you include or exclude the remote portion of the object. For more info, see Stretch Database.

The mode argument can have the following values.

ValueDescription
ALLReturns the storage statistics of the object or database including both the local portion and the remote portion.
LOCAL_ONLYReturns the storage statistics of only the local portion of the object or database. If the object or database is not Stretch-enabled, returns the same statistics as when @mode = ALL.
REMOTE_ONLYReturns the storage statistics of only the remote portion of the object or database. This option raises an error when one of the following conditions is true:

The table is not enabled for Stretch.

The table is enabled for Stretch, but you have never enabled data migration. In this case, the remote table does not yet have a schema.

The user has manually dropped the remote table.

The provisioning of the remote data archive returned a status of Success, but in fact it failed.

mode is varchar(11), with a default of N'ALL'.

[ @oneresultset=] oneresultset
Indicates whether to return a single result set. The oneresultset argument can have the following values.

ValueDescription
0When @objname is null or is not specified, two result sets are returned. This is the current and default behavior.
1When @objname = null or is not specified, a single result set is returned.

oneresultset is bit, with a default of 0.

0 (success) or 1 (failure)

If objname is omitted and the value of oneresultset is 0, the following result sets are returned to provide current database size information.

Column nameData typeDescription
database_namenvarchar(128)Name of the current database.
database_sizevarchar(18)Size of the current database in megabytes. database_size includes both data and log files.
unallocated spacevarchar(18)Space in the database that has not been reserved for database objects.
Column nameData typeDescription
reservedvarchar(18)Total amount of space allocated by objects in the database.
datavarchar(18)Total amount of space used by data.
index_sizevarchar(18)Total amount of space used by indexes.
unusedvarchar(18)Total amount of space reserved for objects in the database, but not yet used.

If objname is omitted and the value of oneresultset is 1, the following single result set is returned to provide current database size information.

Column nameData typeDescription
database_namenvarchar(128)Name of the current database.
database_sizevarchar(18)Size of the current database in megabytes. database_size includes both data and log files.
unallocated spacevarchar(18)Space in the database that has not been reserved for database objects.
reservedvarchar(18)Total amount of space allocated by objects in the database.
datavarchar(18)Total amount of space used by data.
index_sizevarchar(18)Total amount of space used by indexes.
unusedvarchar(18)Total amount of space reserved for objects in the database, but not yet used.

If objname is specified, the following result set is returned for the specified object.

Column nameData typeDescription
namenvarchar(128)Name of the object for which space usage information was requested.

The schema name of the object is not returned. If the schema name is required, use the sys.dm_db_partition_stats or sys.dm_db_index_physical_stats dynamic management views to obtain equivalent size information.
rowschar(20)Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.
reservedvarchar(18)Total amount of reserved space for objname.
datavarchar(18)Total amount of space used by data in objname.
index_sizevarchar(18)Total amount of space used by indexes in objname.
unusedvarchar(18)Total amount of space reserved for objname but not yet used.

database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.

Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. When objname is specified, the pages for the XML indexes and full-text indexes for the object are also counted in the total reserved and index_size results.

If space usage is calculated for a database or an object that has a spatial index, the space-size columns, such as database_size, reserved, and index_size, include the size of the spatial index.

When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.

System_CAPS_ICON_note.jpg Note


When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.

Permission to execute sp_spaceused is granted to the public role. Only members of the db_owner fixed database role can specify the @updateusage parameter.

A. Displaying disk space information about a table

The following example reports disk space information for the Vendor table and its indexes.

USE AdventureWorks2012;  
GO  
EXEC sp_spaceused N'Purchasing.Vendor';  
GO  

B. Displaying updated space information about a database

The following example summarizes space used in the current database and uses the optional parameter @updateusage to ensure current values are returned.

USE AdventureWorks008R2;  
GO  
EXEC sp_spaceused @updateusage = N'TRUE';  
GO  

C. Displaying space usage information about the remote table associated with a Stretch-enabled table

The following example summarizes the space used by the remote table associated with a Stretch-enabled table by using the @mode argument to specify the remote target. For more info, see Stretch Database.

USE StretchedAdventureWorks2016  
GO  
EXEC sp_spaceused N'Purchasing.Vendor', @mode = 'REMOTE_ONLY'  

D. Displaying space usage information for a database in a single result set

The following example summarizes space usage for the current database in a single result set.

USE AdventureWorks2016  
GO  
EXEC sp_spaceused @oneresultset = 1  

CREATE INDEX (Transact-SQL)
CREATE TABLE (Transact-SQL)
DBCC UPDATEUSAGE (Transact-SQL)
SQL Server Service Broker
sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)
sys.objects (Transact-SQL)
sys.partitions (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft