Monitor database growth - a better way for sp_spaceused
This script is for anyone who has had issues in capturing the output from sp_spaceused, which returns two rows of different number of columns, therefore making it hard to capture into a table easily. I ran exec sp_helptext sp_spaceused and found the section where the two rows returned (I'm still confused why there are two select statements, one after the other, that don't have to be separated).
I did not keep the @updateusage portion of sp_spaceused and neither did I try to preserve the table check either via @objname (not needed for my purposes)
UPDATE: Oct 22, 2008 - Had a superflous variable here and there, so just cleaned up the code.
-- This is designed only for 2005 (should work in 2008, not tested there
-- yet. It should be trivial to run a sp_helptext sp_spaceused in
-- SQL 2000 and modify the code accordingly.
use <YOUR DATABASE HERE>
go
set nocount on
declare @dbsize bigint,
@logsize bigint,
@reservedpages bigint,
@usedpages bigint,
@pages bigint
select @dbsize = sum(convert(bigint, case when status & 64 = 0 then size else 0 end)),
@logsize = sum(convert(bigint, case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles
select @reservedpages = sum(a.total_pages), @usedpages = sum(a.used_pages),
@pages = sum(
case
when it.internal_type IN (202, 204) then 0
when a.type <> 1 then a.used_pages
when p.index_id < 2 then a.data_pages
else 0
end
)
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
select db_name(),
cast(((@dbsize + @logsize) * 8192/1048576.) as decimal(15, 2)) "DB Size(MB)",
(case when @dbsize >= @reservedpages then cast(((@dbsize - @reservedpages) * 8192/1048567.) as decimal(15, 2)) else 0 end) "Unalloc. Space(MB)",
cast((@reservedpages * 8192/1048576.) as decimal(15, 2)) "Reserved(MB)",
cast((@pages * 8192/1048576.) as decimal(15, 2)) "Data Used(MB)",
cast(((@usedpages - @pages) * 8192/1048576.) as decimal(15, 2)) "Index Used(MB)",
cast(((@reservedpages - @usedpages) * 8192/1048576.) as decimal(15, 2)) "Unused(MB)"
go
-- A quick way to monitor server growth is create a table
-- the above columns plus a time stamp and something like:
--
-- insert into DBGrowthTable(dbname, dbsizeMB, unallocMB, reservedMB,
-- dataMB, unusedDB, getdate())
-- exec sp_msforeachdb 'use ?; INSERT_ENTIRE_SCRIPT_HERE'
--
-- and run it as a job once each night.
