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 <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 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 INTOtb_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 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 indidin (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 ELSE0 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 THENa.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