Capturing Database(s) and Table Usage stats – sp_spaceused simulation using DMVs
Introduction
The sp_spaceused is a system stored procedure frequently used by DBAs to check the space used by database/tables. This procedure can be executed either with or without parameters.
This article awarded for Technet GURU Silver Medal. You are welcome to update the article as needed
Objective
This article demonstrates a simple process to save 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 tell 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 space used for a database. In order to perform this, execute the procedure with no parameters
USE <DatabaseName>GOsp_spaceusedFor Example:-
USE EMPLOYEEGOsp_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
https://gallery.technet.microsoft.com/site/view/file/130101/1/sp_spaceUsed2.jpg
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 the procedure is called with the valid object, a 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>GOsp_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
https://gallery.technet.microsoft.com/site/view/file/130102/1/sp_spaceUsed3.jpg
- 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 the 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 give 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 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 a 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
OUTPUT:-
SQL 2000
The below code can be used to get sp_spaceused output in a 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
OUTPUT:
Conclusion
- Capture the database and table usage data which ease out to assess 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
- http://msdn.microsoft.com/en-us/library/ms188776.aspx
- http://stackoverflow.com/questions/6843744/results-form-sp-spaceused-in-one-recordset
- http://raresql.wordpress.com/2013/02/27/sql-server-how-to-run-sp_spaceused-for-all-tables-in-a-database/
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