Hi folks, today in SQL Server system stored procedure series you will learn about how to get information about allocated and unallocated space in database and database objects using stored procedure sp_spaceused.
Sp_spaceused stored procedure displays the disk space used by database, disk space used by table, indexed view, number of rows in a table. If objectname is not specified then it gives information about space used by current database.
Syntax of sp_spaceused is as follows:
sp_spaceused [[ @objname = ] 'objname' ] [,[ @updateusage = ] 'updateusage' ]
Arguments
@objname is the name of table, indexed view whose space information is required. Objname is nvarchar(776) and has default value of NULL. When objname is not specified, it displays the information about database.
@updateusage used to indicates whether DBCC UPDATEUSAGE should be run or not. It can store values TRUE or FALSE having datatype varchar(5) with default value FALSE.
Now let’s run the procedure at database level and object level.
EXEC sp_spaceused
When you run procedure without specifying any object two datasets returned in result sets.
First result set shows the following information:
Database_name – Name of database
Database_size – size of current database.
Unallocated Space – Unreserved space for database object.
Second result set shows following information:
Reserved – Total reserved space in a database.
Data – Total space used by database
Index_size – Total space used by indexed in a database.
Unused – Total reserved space for objects in a database.
Now, let’s run this procedure for a table:
EXEC sp_spaceused [Production.Products]
Name – Name of table
Rows – Number of rows in a table
Reserved – Total reserved space of an object
Data – Total space used by table
Index_size – Total space used by index in a table.
Unused – Total reserved space used by table.
That’s all folks for the day. Hope you like it.
Regards,
Kapil Singh
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
