Introduction
Objective
- SP_SPACEUSED - DATABASE
- SP_SPACEUSED - TABLE
Permission
SQL
Customized code for specific database
Capturing details in a permanent table of databases using DMV’s
Displaying all user defined table usage information
Conclusion
Reference

Introduction

sp_spaceused is one of the system stored procedure used frequently by many DBA’s that reveals amount of space used by database/tables. This procedure can be executed either by passing parameters or no parameters. The objective of this stored procedure is to measure the amount of space consumed by database or tables.

Objective

This article demonstrates a simple process that saves database usage information in a single result set. This is a simulation of sp_spaceused using DMV’s. It gives db usage information of all dbs in a single result set also the output includes two more extra columns which tells data and log file size. You can also customize the code to capture db usage information for specific databases. The process is useful in monitoring DB growth over time and lets you see what databases are growing rapidly also help in estimating future growth and disk space requirements. The SQL output can be stored in a table which allows us to estimate future growth and helps in forecasting the disk space requirement over time. In some cases the results are not accurate and it requires update system views.

SP_SPACEUSED - DATABASE

The most common usage for sp_spaceused is to measure the amount of spaced used for a database. In order to perform this, execute the procedure with no parameters

USE <DatabaseName>
GO
sp_spaceused
For Example:-

USE EMPLOYEE
GO
sp_spaceused

This output will be displayed two result sets and it returns the following information

First:-

Current database name
Current database size
Unallocated space

Second:-

Reserved space
Space used by data
Space used by indexes
Unused space

First Result set:

database_name: Name of the current database
database_size: Size of the current database in MegaBytes
database_size = data files+log files
unallocated space: Space in the database that has not been reserved for database objects

Second Result set:

reserved: Total amount of space allocated by objects in the database
data: Total amount of space used by data
index_size: Total amount of space used by indexes
unused: Total amount of space reserved for objects in the database, but not yet used

SP_SPACEUSED - TABLE

If procedure is called with valid object, single result set is returned for the specific object and it displays the number of rows, disk space reserved, and disk space used by a table, indexed views.

USE <Database Name>
GO
sp_spaceused [[ @objname = ] 'objname' ]
For Example:-

USE EMPLOYEE

GO

sp_spaceused

'Production.ProductInventory'

It returns the following information

Name of the Table
No of rows of the table
Reserved space
Space used by data
Space used by indexes
Unused space

name : Table name
rows : Number of rows of the given table
reserved : Total amount of reserved space [data + index]
data : Amount of space used by table
index_size : Amount of space used by table indexes
Unused : Total amount of space reserved for table but no yet used

Permission

Permission to execute sp_spaceused is granted to the public role. Only members of the db_owner fixed database role can specify the @updateusage parameter.

SQL

The output gives db usage information of all dbs in a single result set also the output includes two more extra columns which gives data and log file sizes

--Displaying the usage details of all the databases.

DECLARE @allocation_table table

(

dbname sysname,

reservedpages

bigint,

usedpages

bigint,

pages

bigint

)

INSERT INTO


@allocation_table

EXEC sp_MSforeachdb N'IF EXISTS

(

SELECT 1 FROM SYS.DATABASES WHERE name = ''?'' AND NAME NOT IN(''master'',''msdb'',''model'',''tempdb'

')
 and STATE=0

--customize to monitor specific databases

--SELECT 1 FROM SYS.DATABASES WHERE name = ''?'' AND NAME IN(''EMPLOYEE'

')
 and STATE=0

)

BEGIN

SELECT

''?'',

SUM(a.total_pages) as reservedpages,

SUM(a.used_pages) as usedpages,

SUM(

CASE

-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"

When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0

When a.type <> 1 Then a.used_pages

When p.index_id < 2 Then a.data_pages

Else 0

END

) as pages

from ?.sys.partitions p join ?.sys.allocation_units a on p.partition_id = a.container_id

left join ?.sys.internal_tables it on p.object_id = it.object_id

END';

SELECT

-- from first result set of 'exec sp_spacedused'

db_name(sf.database_id)

as


[database_name]

,ltrim(str((convert (dec (15,2),sf.dbsize) + convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB'

as


[database_size]

,ltrim(str((case when


sf.dbsize >= pages.reservedpages

then

(convert (dec (15,2),sf.dbsize) - convert (dec (15,2),pages.reservedpages))

* 8192 / 1048576

else

end

),15,2) +

' MB') as


[unallocated

space]

-- from second result set of 'exec sp_spacedused'

,ltrim(str(pages.reservedpages * 8192 / 1024.,15,0) +

' KB') as


[reserved]

,ltrim(str(pages.pages * 8192 / 1024.,15,0) +

' KB') as


data

,ltrim(str((pages.usedpages - pages.pages) * 8192 / 1024.,15,0) +

' KB') as


index_size

,ltrim(str((pages.reservedpages - pages.usedpages) * 8192 / 1024.,15,0) +

' KB') as


unused

-- additional columns data and Log Size

,ltrim(str((convert (dec (15,2),sf.dbsize)) * 8192 / 1048576,15,2) + ' MB'

as


dbsize

,ltrim(str((convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB'

as


logsize

FROM (

select

database_id,

sum(convert(bigint,case when


type = 0

then


size

else

end

))

as


dbsize,

sum(convert(bigint,case when


type <> 0

then


size

else

end

))

as


logsize

from sys.master_files

group by


database_id

) sf,

(

SELECT

dbname,

reservedpages,

usedpages,

pages

FROM @ALLOCATION_TABLE

) pages

WHERE DB_NAME(sf.database_id)=pages.dbname

Customized code for specific database

Change the below shown line from an above given code for any customization. For example, the code below ran against EMPLOYEE database also the below screen show comparison of outputs from DMV's v/s sp_spaceused

Capturing details in a permanent table of databases using DMV’s

The database usage information that we gather contains guidelines to help you plan and configure the storage and SQL Server database

This process defined in three steps

Create permanent table
Execute the SQL
Display the result

/******************************************************************************************************
The table tb_SpaceUsed is created to gather the details periodically
******************************************************************************************************/

CREATE TABLE


tb_SpaceUsed

(

Database_Name sysname,

database_sizeMB

decimal(7,2),

Unallocated_SpaceMB

decimal(7,2),

reservedKB

bigint,

dataKB

bigint,

Index_SizeKB

bigint,

unusedKB

bigint,

dbSizeMB

decimal(7,2),

logSizeMB

decimal(7,2),

logdate

int


default

(CONVERT(varchar(10), getdate(),112))

)

/******************************************************************************************************

--@allocation_table variable is used to gather allocation units details of all the databases

******************************************************************************************************/

DECLARE @allocation_table table

(

dbname sysname,

reservedpages

bigint,

usedpages

bigint,

pages

bigint

)

INSERT INTO


@allocation_table

EXEC sp_MSforeachdb N'IF EXISTS

(

SELECT 1 FROM SYS.DATABASES WHERE name = ''?'' AND NAME NOT IN(''master'',''msdb'',''model'',''tempdb'

')
 and STATE=0

--customize to monitor specific databases

--SELECT 1 FROM SYS.DATABASES WHERE name = ''?'' AND NAME IN(''EMPLOYEE'

')
 and STATE=0

)

BEGIN

SELECT

''?'',

SUM(a.total_pages) as reservedpages,

SUM(a.used_pages) as usedpages,

SUM(

CASE

-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"

When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0

When a.type <> 1 Then a.used_pages

When p.index_id < 2 Then a.data_pages

Else 0

END

) as pages

from ?.sys.partitions p join ?.sys.allocation_units a on p.partition_id = a.container_id

left join ?.sys.internal_tables it on p.object_id = it.object_id

END';

/******************************************************************************************************

--Inserting the db usage information to tb_SpaceUsed table

******************************************************************************************************/

INSERT INTO


tb_SpaceUsed(Database_Name,database_sizeMB,Unallocated_SpaceMB,reservedKB,dataKB,Index_SizeKB,unusedKB,dbSizeMB,logSizeMB)

SELECT

-- from first result set of 'exec sp_spacedused'

db_name(sf.database_id)

as


[database_name]

,ltrim(str((convert (dec (15,2),sf.dbsize) + convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) ) as [database_size]

,ltrim(str((case when


sf.dbsize >= pages.reservedpages

then

(convert (dec (15,2),sf.dbsize) - convert (dec (15,2),pages.reservedpages))

* 8192 / 1048576

else

end

),15,2) )

as


[unallocated

space]

-- from second result set of 'exec sp_spacedused'

,ltrim(str(pages.reservedpages * 8192 / 1024.,15,0) )

as


[reserved]

,ltrim(str(pages.pages * 8192 / 1024.,15,0) )

as


data

,ltrim(str((pages.usedpages - pages.pages) * 8192 / 1024.,15,0) )

as


index_size

,ltrim(str((pages.reservedpages - pages.usedpages) * 8192 / 1024.,15,0) )

as


unused

-- additional columns data and Log Size

,ltrim(str((convert (dec (15,2),sf.dbsize)) * 8192 / 1048576,15,2) ) as dbsize

,ltrim(str((convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2)) as logsize

FROM (

select

database_id,

sum(convert(bigint,case when


type = 0

then


size

else

end

))

as


dbsize,

sum(convert(bigint,case when


type <> 0

then


size

else

end

))

as


logsize

from sys.master_files

group by


database_id

) sf,

(

SELECT

dbname,

reservedpages,

usedpages,

pages

FROM @ALLOCATION_TABLE

) pages

WHERE DB_NAME(sf.database_id)=pages.dbname

/******************************************************************************************************
-- Displaying Output
******************************************************************************************************/

select * from tb_SpaceUsed

OUTPUT:-

SQL 2000

The below code can be used to get sp_spaceused output in single result set for SQL 2000 instances. The code is a written from a clone of sp_spaceused stored procedure with dynamic sql.

/*

Create temp


TABLEs before

any

DML

to


ensure

dynamic

We need

to


CREATE

a temp TABLE

to

do the calculation.

reserved:

sum

(reserved)

WHERE


indid

in (0, 1, 255)

DATA:

sum

(dpages)

WHERE


indid < 2 +

sum

(used)

WHERE


indid = 255 (text)

indexp:

sum(used) WHERE


indid

in (0, 1, 255) - DATA

unused:

sum

(reserved) -

sum(used) WHERE


indid

in (0, 1, 255)

*/

---------------------------------------------------------------------------------------------------

-- Desc. : created a automated script to log's all space details of different database.

---------------------------------------------------------------------------------------------------

DECLARE @pages INT

DECLARE @dbname sysname

DECLARE @dbsize DEC(15,0)

DECLARE @logsize DEC(15)

DECLARE @bytesperpage DEC(15,0)

DECLARE @pagesperMB DEC(15,0)

DECLARE @DML1 nvarchar(2000)

DECLARE @DML2 nvarchar(200)

DECLARE @DML3 nvarchar(200)

DECLARE @DML4 nvarchar(200)

DECLARE @DML5 nvarchar(200)

DECLARE @DML6 nvarchar(200)

DECLARE @DML7 nvarchar(200)

DECLARE @DML8 nvarchar(200)

DECLARE @DML9 nvarchar(200)

DECLARE @DML10 nvarchar(200)

DECLARE @DML11 nvarchar(200)

DECLARE @DML12 nvarchar(1000)

DECLARE @DML13 nvarchar(4000)

DECLARE @LoopStatus int

DECLARE @RowId int

DECLARE @dbname_1 varchar(100)

DECLARE @DML14 varchar(2000)

SET @DML14='

DECLARE @pages INT

,@dbname sysname,@dbsize

DEC

(15,0),@logsize

DEC

(15),@bytesperpage

DEC

(15,0),@pagesperMB

DEC(15,0),

@DML1 nvarchar(2000),@DML2 nvarchar(200),@DML3 nvarchar(200),@DML4 nvarchar(200),@DML5 nvarchar(200),@DML6 nvarchar(200),

@DML7 nvarchar(200),@DML8 nvarchar(200),@DML9 nvarchar(200),@DML10 nvarchar(200),@DML11 nvarchar(200),@DML12 nvarchar(800),

@DML13 nvarchar(2000),@LoopStatus

int

,@RowId

int

,@dbname_1

varchar(100)'

CREATE TABLE #growthRate

(

SlNo int identity(1,1) primary key,

DatabaseName varchar(25),

Databasesize DECIMAL(10,2),

UnallocatedSpace DECIMAL(10,2),

Reserved INT,

Data INT,

Indexsize INT,

unused INT

)

CREATE TABLE #spt_space

(

rows INT NULL,

reserved DEC(15) NULL,

DATA DEC(15) NULL,

indexp DEC(15) NULL,

unused DEC(15) NULL

)

CREATE TABLE #Database_List

(

id int identity(1,1) PRIMARY KEY,

DatabaseName VARCHAR(200)

)

INSERT INTO #Database_List VALUES('pubs')

INSERT INTO #Database_List VALUES('Load')

INSERT INTO #Database_List VALUES('Hist')

--INSERT INTO #Database_List VALUES('Tempdb')

SET @dbsize=1

SET @logsize=1

SET @bytesperpage=1

SET @pagesperMB=1

SET @pages=1

SET @LoopStatus=1

SET @RowId=1

WHILE( @LoopStatus<>0)

BEGIN

SELECT @dbname_1=DatabaseName FROM #Database_List WHERE id=@RowId

IF @@ROWCOUNT=0

BEGIN

SET @LoopStatus=0

END

ELSE

BEGIN

TRUNCATE TABLE #spt_space

SET @DML2='SELECT @dbsize = sum(convert(DEC(15),size

))

FROM


dbo.sysfiles

WHERE


(status & 64 = 0)

'+CHAR(13)

SET @DML3='SELECT @logsize = sum(convert(DEC(15),size))FROM dbo.sysfiles WHERE (status & 64 <> 0)'+CHAR(13)

SET @DML4='SELECT @bytesperpage = low

FROM master.dbo.spt_values

WHERE number = 1 and type = ''E'''+CHAR(13)

SET @DML5='SELECT @pagesperMB = 1048576 / @bytesperpage'+char(13)

SET @DML6='INSERT INTO


#spt_space (reserved)

SELECT sum(convert(DEC

(15),reserved))

FROM


sysindexes

WHERE


indid

in (0, 1, 255)'+CHAR(13)

SET @DML7='SELECT @pages = sum(convert(DEC

(15),dpages))

FROM


sysindexes

WHERE


indid < 2

'+CHAR(13)

SET @DML8='SELECT @pages = @pages + ISNULL(sum(convert(DEC

(15),used)),
 0)

FROM


sysindexes

WHERE


indid = 255

'+CHAR(13)

SET @DML9='

UPDATE #spt_space

SET DATA =@pages'+CHAR(13)

SET @DML10='

UPDATE #spt_space

SET indexp = (SELECT sum(convert(DEC

(15),used))

FROM


sysindexes

WHERE


indid

in (0, 1, 255)) - DATA'+CHAR(13)

SET @DML11='

UPDATE #spt_space

SET unused = reserved - (SELECT sum(convert(DEC(15),used))

FROM sysindexes WHERE indid in


(0, 1, 255))

'+CHAR(13)

SET @DML12='

INSERT INTO


#growthRate(DatabaseName,Databasesize ,UnallocatedSpace,Reserved,Data,Indexsize,unused)

SELECT

DatabaseName = db_name(),

DatabaseSize = ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2)),

unallocatedspace = ltrim(str((@dbsize - (SELECT sum(convert(DEC(15),reserved))FROM sysindexes WHERE indid in


(0, 1, 255))) / @pagesperMB,15,2)),

Reserved = ltrim(str(reserved * d.low / 1024,15,0)),

Data = ltrim(str(DATA * d.low / 1024,15,0)),

IndexSize = ltrim(str(indexp * d.low / 1024,15,0)),

Unused = ltrim(str(unused * d.low / 1024,15,0))

FROM

#spt_space, master.dbo.spt_values d

WHERE

d.number = 1

AND

d.type =

''E'''

SET @DML1='USE'+' '+@dbname_1+ char

(13)+

char(13)+@DML14+char(13)+Char(13)

SET @DML13=@DML1+@DML2+@DML3+@DML4+@DML5+@DML6+@DML7+@DML8+@DML9+@DML10+@DML11+@DML12

EXEC sp_executesql @DML13

END

SET @RowId=@RowId+1

END

SELECT * FROM #growthRate

DROP TABLE


#spt_space

DROP TABLE


#growthRate

DROP TABLE


#Database_List

Displaying all user defined table usage information

This process is useful in monitoring table growth over time and lets you see what tables are growing rapidly also help in estimating future growth and disk space requirements. The SQL output can be stored in a table which allows us to estimate future growth and helps in forecasting the disk space requirement over time.

USE <DBNAME>

GO

DECLARE @PageSize float

select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'

SELECT

object_Name(i.object_id)

as

name]

,p.rows

,Convert(varchar

(50),@PageSize
 *

SUM

(total_pages)) +

' KB'

as

[reserved]

,Convert(varchar

(50),@PageSize
 *

SUM(CASE WHEN


a.type <> 1

THEN


a.used_pages

WHEN


p.index_id < 2

THEN


a.data_pages

ELSE

END

)) +

' KB'

as

[data]

,Convert(varchar

(50),@PageSize
 *

SUM

(a.used_pages -

CASE


WHEN

a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END

)) +

' KB'

as

[index_size]

,Convert(varchar

(50),@PageSize
 *

SUM

(total_pages-used_pages)) +

' KB'

as

[unused]

FROM sys.indexes as i

JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

JOIN sys.allocation_units as a ON a.container_id = p.partition_id

JOIN sys.tables t ON i.object_id=t.object_id

Where i.type<=1 and a.type=1

and

t.type='U' and is_ms_shipped=0

GROUP BY


i.object_id,p.

rows

OUTPUT:-

Conclusion

Capture the database and table usage data which ease out to asses on-going demand and leaves out room for future growth
Better understanding of data and file growth at granular level
Easily analyze the growth trend for various pattern such as linear, non-linear and Exponential growth
We can estimate the requirement and do a better forecasting
One place to get sp_spaceused output into single result set for all SQL version

Reference

DECLARE @allocation_table table

(

dbname sysname,

reservedpages bigint,

usedpages bigint,

pages bigint

)

INSERT INTO @allocation_table

EXEC sp_MSforeachdb N'IF EXISTS

(

SELECT 1 FROM SYS.DATABASES WHERE name = ''?'' AND NAME NOT IN(''master'',''msdb'',''model'',''tempdb'

')
 and STATE=0

--customize to monitor specific databases

--SELECT 1 FROM SYS.DATABASES WHERE name = ''?'' AND NAME IN(''EMPLOYEE'') and STATE=0

)

BEGIN

SELECT

''?'',

SUM(a.total_pages) as reservedpages,

SUM(a.used_pages) as usedpages,

SUM(

CASE

-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"

When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0

When a.type <> 1 Then a.used_pages

When p.index_id < 2 Then a.data_pages

Else 0

END

) as pages

from ?.sys.partitions p join ?.sys.allocation_units a on p.partition_id = a.container_id

left join ?.sys.internal_tables it on p.object_id = it.object_id

END';

SELECT

-- from first result set of 'exec sp_spacedused'

db_name(sf.database_id) as [database_name]

,ltrim(str((convert (dec

(15,2),sf.dbsize)
 +

convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB') as[database_size]

,ltrim(str((case when sf.dbsize >= pages.reservedpages then

(convert (dec

(15,2),sf.dbsize)
 -

convert (dec (15,2),pages.reservedpages))

* 8192 / 1048576 else 0 end

),15,2)
 +

' MB') as [unallocated space]

-- from second result set of 'exec sp_spacedused'

,ltrim(str(pages.reservedpages * 8192 / 1024.,15,0) + ' KB') as [reserved]

,ltrim(str(pages.pages * 8192 / 1024.,15,0) + ' KB') as data

,ltrim(str((pages.usedpages - pages.pages) * 8192 / 1024.,15,0) + ' KB') as index_size

,ltrim(str((pages.reservedpages - pages.usedpages) * 8192 / 1024.,15,0) + ' KB') as unused

-- additional columns data and Log Size

,ltrim(str((convert (dec

(15,2),sf.dbsize))
 * 8192 / 1048576,15,2) +

' MB') as dbsize

,ltrim(str((convert (dec

(15,2),sf.logsize))
 * 8192 / 1048576,15,2) +

' MB') as logsize

FROM (

select

database_id,

sum(convert(bigint,case when

type
 = 0

then size else 0 end)) as dbsize,

sum(convert(bigint,case when

type
 <> 0

then size else 0 end)) as logsize

from sys.master_files

group by database_id

) sf,

(

SELECT

dbname,

reservedpages,

usedpages,

pages

FROM @ALLOCATION_TABLE

) pages

WHERE DB_NAME(sf.database_id)=pages.dbname

| |

Comments

Shanky_621

27 Nov 2014 6:48 AM

Prash Can you remove the images and take screenshot again and upload it the images are not clear. The last two images are not good as query is selected and its all in blue make sure queries are visible
Mr. Zator

27 Nov 2014 6:55 AM

thanks
Prashanth Jayaram

28 Nov 2014 11:18 AM

I've uploaded the new images.Thanks again Shanky.

Capturing Database(s) and Table Usage stats – sp_spaceused simulation using DMVs

Capturing Database(s) and Table Usage stats – sp_spaceused simulation using DMVs

Table of Contents

Introduction

Objective

SP_SPACEUSED - DATABASE

SP_SPACEUSED - TABLE

Permission

SQL

Customized code for specific database

Capturing details in a permanent table of databases using DMV’s

SQL 2000

Displaying all user defined table usage information

Conclusion

Reference

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?