SP_SPACEUSED–Determine size (Space) – SQL Server TIP #1

 

To determine size of table in a database or size of database, we have simple syntax in SQL Server which is SP_SPACEUSED.

Syntax is simple enough as shown below

1) If we want to determine size of the database then we have to write below statement and execute

Use DatabaseName

GO

SP_SPACEUSED

GO

sp_spaceused_DB

2) Now to determine size of particular table  we need to write following syntax

GO

SP_SPACEUSED TABLENAME

GO

sp_spaceused_tableName_Rajat_Jaiswal

3) Now to determine size of each table in a database we need to write following syntax

GO

SP_MSFOREACHTABLE ‘SP_SPACEUSED [1]’

GO

sp_MSForEachTable_sp_spaceUsed_Indiandotnet

4) When we are running sp_spaceused it may possible the size is not updated so to make sure size is updated we need to write following command

GO

SP_SPACEUSED @updateUsage =N’True’

GO

sp_spaceused_Update_Usage

In the output of each statements we are seeing reserved, data,Index_Size & unused columns.

so

1) reserved column shows total space reserved by object.

2) data column shows total space used by the data.

3) index_Size shows total space used by index in object.

4)unused shows total space reserved by object but not yet used.

For more information you can visit following link

http://msdn.microsoft.com/en-us/library/ms188776.aspx

Enjoy.

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