In focus

How To Get Space Used By Database Objects

In this article we learn how to get space-used information on database objects. Sp_spaceused procedure ensures getting space-used information to us.

Gagan Sharma Apr 20, 2016

To get space-used information on database objects, we use Sp_Spaceused procedure, it providse space-used information. This command is used without any parameters to get database size information.

Before executing Sp_Spaceused procedurewe  must assure that permission to execute sp_spaceused is granted to the user of  the db_owner fixed database role group.

In the SQL Server Management Studio, Write the SQL statement.

Query:  
  1. USE [AdventureWorks2014]  
  2. GO     
  3. EXEC sp_spaceused  
  4. GO EXEC sp_spaceused  
Result:  Here we will get the space-used details about the database named  'AdventureWorks2014'.
a1

Details of recordset:
  • database_name: It is the current database name.

  • database_size: Size of the current database in megabytes. It includes data and log files.

  • unallocated space: Space in the database that is not reserved for database objects.

  • reserved: It is the space allocated by objects in the database.

  • data: It shows the total amount of space used by data.

  • index_size: it shows the total amount of space used by indexes.

  • unused: It shows the total amount of space reserved for objects in the database, that is not used yet.
To find out in the current database, which table take the most disk space. We need to use the Sp_Spaceused stored procedure so that we can determine the amount of disk space (data and index) is utilized.

Query: 
  1. EXEC sp_spaceused 'person.person'  
Result:  Here 'person.person' is the table name for which we are getting information.
a2
 

Details of recordset:
  • name: It is the table name .

  • rows: Count of  rows existing in the table.

  • reserved: Total space reserved for table data and indexes.

  • data: It is the space used by table data.

  • index_size: It is the space used by table indexes.

  • unused: It is the total amount of space reserved for table, that is not used yet.

database database objects objects

COMMENT USING