Posted in Stored Procedure

Sp_spaceused stored procedure in SQL Server

Hi folks, today 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’ ]


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



I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s