For more Information refer Technet Article
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 <
Database
Name
>
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'
]
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
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
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
0
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
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
/******************************************************************************************************
— Displaying Output
******************************************************************************************************/
select
*
from
tb_SpaceUsed
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
0
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
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