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:


@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.


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:


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.


Kapil Singh

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

Follow me on Twitter