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:
- USE [AdventureWorks2014]
- GO
- EXEC sp_spaceused
- GO EXEC sp_spaceused
Result: Here we will get the space-used details about the database named 'AdventureWorks2014'.
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:
- EXEC sp_spaceused 'person.person'
Result: Here 'person.person' is the table name for which we are getting information.
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.