Sp_spaceused – Day 9 – SQL Server System Stored Procedure

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

sp_spaceused1

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]

sp_spaceused2

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 

Follow me on Twitter

 

 

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

Leave a Reply

Your email address will not be published. Required fields are marked *