Sunday, November 2, 2014

Understanding SQL Server sp_spaceused for database size


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.

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



'objname'Is the name of the table, indexed view, or queue for
which space usage information is requested.

Permission to execute sp_spaceused is granted to the public role.


'updateusage'Indicates DBCC UPDATEUSAGE should be run to update
space usage information.

Only members of the db_owner fixed database role can specify 
the @updateusage parameter.

Result Sets:

1) database_name
   Name of the current database

2) database_size
  i) Size of the current database in megabytes. 
 ii) database_size includes both data and log files.

3) unallocated space
  i) Space in the database that has not been reserved for database

4) reserved
 Total amount of space allocated by objects in the database.
 Size in Kilobytes

5) data

   Total amount of space used by data.  Size in Kilobytes

6) index_size

   Total amount of space used by indexes. Size in Kilobytes

7) Unused
 Total amount of space reserved for objects in the database,but 
 not yet used. Size in Kilobytes

No comments :

Post a Comment