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’ ]

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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s