Found this thread and thought I'd post my version for anyone still following it
HTH
Dave J
/****** Object: StoredProcedure [dbo].[usp_Check_DB_growth] Script Date: 06/14/2011 10:14:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Check_DB_growth] @initialise INT = NULL
AS
/*
This can be setup on a weekly/monthly schedule or run on an ad-hoc basis
On the first run use
exec usp_Check_DB_growth @initialise = 1
and it will create a table called DBGrowthRate in the current DB
New databases will be picked up if they are added over time
Note: The first results will always show 0 Growth as you need to run it at least twice,
with one database having grown in between, to see any meaningful results.
Based on a script found at http://www.sqlservercentral.com/Forums/Topic401684-513-1.aspx
Dave Jackson
*/
SET NOCOUNT ON
IF @initialise = 1
BEGIN
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'DBGrowthRate' AND TYPE = 'U')
BEGIN
Raiserror('The table DBGrowthRate already exists in this database. Please drop it manually to re-create.', 18,1)
Return
END
CREATE TABLE dbo.DBGrowthRate
(
dbgrowthid INT IDENTITY(1, 1),
dbname VARCHAR(100),
[DBID] INT,
numpages INT,
origsize DECIMAL(10, 2),
cursize DECIMAL(10, 2),
growthamt DECIMAL(10, 2),
metricdate DATETIME
)
END
IF NOT EXISTS (SELECT name FROM sys.objects WHERE name = 'DBGrowthRate' AND TYPE = 'U')
BEGIN
Raiserror('The table DBGrowthRate does not exists in this database. Please run this procedure with the @initialise parameter = 1.', 18,1)
Return
END
-- Check for any new Databases and add them if there are any
If Exists(Select 1 FROM sys.databases sd
WHERE sd.database_id NOT IN (SELECT DISTINCT [DBID] FROM DBGrowthRate))
BEGIN
SELECT sd.name AS dbname,
mf.name AS filename,
mf.database_id,
file_id,
size
INTO #tempdbsize
FROM sys.databases sd
JOIN sys.master_files mf
ON sd.database_id = mf.database_id
ORDER BY mf.database_id,
sd.name
INSERT INTO dbo.DBGrowthRate
(dbname,
[DBID],
numpages,
origsize,
cursize,
growthamt,
metricdate)
SELECT tds.dbname,
tds.database_id,
SUM(tds.size) AS numpages,
CONVERT(DECIMAL(10, 2), (SUM(CONVERT(DECIMAL(10, 2), tds.size)) * 8)/ 1024.0) AS origsize,
CONVERT(DECIMAL(10, 2), (SUM(CONVERT(DECIMAL(10, 2), tds.size)) * 8)/ 1024.0) AS cursize,
0 AS growthamt,
Getdate() AS metricdate
FROM #tempdbsize tds
WHERE tds.database_id NOT IN (SELECT DISTINCT [DBID]
FROM DBGrowthRate)
GROUP BY tds.database_id,
tds.dbname
DROP TABLE #tempdbsize
END
--Below is the code to run periodically to check the growth.
BEGIN
SELECT sd.name AS dbname,
-- mf.name AS filename,
mf.database_id,
file_id,
size
INTO #tempdbsize2
FROM sys.databases sd
JOIN sys.master_files mf
ON sd.database_id = mf.database_id
ORDER BY mf.database_id,
sd.name
IF EXISTS (SELECT DISTINCT dbname
FROM #tempdbsize2
WHERE dbname IN (SELECT DISTINCT dbname
FROM DBGrowthRate))
AND Getdate() > (SELECT DISTINCT MAX(metricdate) AS metricdate
FROM DBGrowthRate)
BEGIN
INSERT INTO dbo.DBGrowthRate
(dbname,
[DBID],
numpages,
origsize,
cursize,
growthamt,
metricdate)
SELECT tds.dbname,
tds.database_id,
SUM(tds.size) AS numpages,
dgr.cursize AS origsize,
CONVERT(DECIMAL(10, 2), (SUM(CONVERT(DECIMAL(10, 2), tds.size)) * 8)/ 1024.0) AS cursize,
CONVERT(DECIMAL(10, 2), (SUM(CONVERT(DECIMAL(10, 2), tds.size)) * 8)/ 1024.0) - dgr.cursize AS growthamt,
Getdate() AS metricdate
FROM #tempdbsize2 tds
JOIN DBGrowthRate dgr
ON tds.database_id = dgr.[DBID]
WHERE dbgrowthid = (SELECT DISTINCT MAX(dbgrowthid)
FROM DBGrowthRate
WHERE [DBID] = dgr.[DBID])
GROUP BY tds.database_id,
tds.dbname,
dgr.cursize,
dgr.origsize
HAVING (CONVERT(DECIMAL(10, 2), (SUM(CONVERT(DECIMAL(10, 2), tds.size)) * 8)/ 1024.0) - dgr.cursize <> 0)
END
DROP TABLE #tempdbsize2
SELECT t1.dbname,
t1.[DBID],
MAX(t1.numpages) [Num Pages],
MAX(t1.origsize) [Orig Size MB],
MAX(t1.cursize) [Cur Size MB],
t1.growthamt [Growth Amt MB],
isNull(MAX(t3.metricdate), MAX(t2.metricdate)) [Last Metric Date],
MAX(t2.metricdate) [Metric Date],
DATEDIFF(d,isNull(MAX(t3.metricdate), MAX(t2.metricdate)), MAX(t2.metricdate)) [Days between Growth]
FROM dbo.DBGrowthRate t1
INNER JOIN (SELECT MAX(metricdate) metricdate,
[DBID]
FROM dbo.DBGrowthRate
GROUP BY [DBID]) t2
ON t1.[DBID] = t2.[DBID]
AND t1.metricdate = t2.metricdate
LEFT JOIN (SELECT metricdate,
[DBID]
FROM dbo.DBGrowthRate) t3
ON t1.[DBID] = t3.[DBID]
AND t3.metricdate < t2.metricdate
GROUP BY t1.dbname,
t1.[DBID],
t1.growthamt
ORDER BY MAX(t2.metricdate) desc
END
GO