Capturing DB Usage stats – sp_spaceused simulation using DMV’s.

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 :-

SQL 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 databasesDECLARE @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_pagesas reservedpages, 
            SUM(a.used_pagesas 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,216Then 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_idas [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 sysnamedatabase_sizeMB decimal(7,2), 
Unallocated_SpaceMB decimal(7,2), 
reservedKB intdataKB intIndex_SizeKB intunusedKB intdbSizeMB 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_pagesas reservedpages, 
            SUM(a.used_pagesas 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,216Then 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_idas [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
 
Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL and tagged , , . Bookmark the permalink.

2 Responses to Capturing DB Usage stats – sp_spaceused simulation using DMV’s.

  1. Andrey says:

    Prashanth, hi!
    Thanks for the script!

    One thing to add :
    This part of script needs to be modified due to issues with databases which have “-” in their names:
    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
    New version:
    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

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