Syntax
sp_spaceused [[ @objname = ] 'objname' ]
[,[ @updateusage = ] 'updateusage' ]
If objname is omitted, the following result sets are returned to provide current database size information.
| Column name | Data type | Description |
|---|---|---|
| database_name | nvarchar(128) | Name of the current database. |
| database_size | varchar(18) | Size of the current database in megabytes. database_size includes both data and log files. |
| unallocated space | varchar(18) | Space in the database that has not been reserved for database objects. |
| Column name | Data type | Description |
|---|---|---|
| reserved | varchar(18) | Total amount of space allocated by objects in the database. |
| data | varchar(18) | Total amount of space used by data. |
| index_size | varchar(18) | Total amount of space used by indexes. |
| unused | varchar(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 name | Data type | Description |
|---|---|---|
| name | nvarchar(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_stats2 or sys.dm_db_index_physical_stats3 dynamic management views to obtain equivalent size information. |
| rows | char(11) | 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. |
| reserved | varchar(18) | Total amount of reserved space for objname. |
| data | varchar(18) | Total amount of space used by data in objname. |
| index_size | varchar(18) | Total amount of space used by indexes in objname. |
| unused | varchar(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.
 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. |
No comments:
Post a Comment