SP_SPACEUSED
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 proc 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. 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.
Download the code :-
Please refer the below link for more information :-
capturing-all-db-s-dbtable-usage-stats-sp-spaceused-simulation-using-dmvs
SQL
--Displaying the usage details of all the databases. DECLARE @allocation_table table ( dbname sysname, reservedpages int, usedpages int, pages int ) 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
Capturing details in a permanent table of all databases using DMV’s
The information that we gather contains guidelines to help you plan and configure the storage and SQL Server database
SQL
--Storing allocation details of all the databases. --The table tb_SpaceUsed is created to gather the details peroidically CREATE TABLE tb_SpaceUsed ( Database_Name sysname, database_sizeMB decimal(7,2), Unallocated_SpaceMB decimal(7,2), reservedKB int, dataKB int, Index_SizeKB int, unusedKB int, 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 int, usedpages int, pages int ) 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 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 the output select * from tb_SpaceUsed
