Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Monitor Database Growth Expand / Collapse
Author
Message
Posted Monday, June 13, 2011 6:28 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 5:31 AM
Points: 441, Visits: 1,790
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
http://glossopian.co.uk

*/

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





http://glossopian.co.uk/
"I don't know what I don't know."
Post #1124195
Posted Monday, June 20, 2011 12:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:01 PM
Points: 5, Visits: 75
even it was not working for me too. as it is showing 0 mb growth rate.
Post #1128456
Posted Tuesday, June 21, 2011 5:34 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 5:31 AM
Points: 441, Visits: 1,790
raj-211912 (6/20/2011)
even it was not working for me too. as it is showing 0 mb growth rate.


I've amended my version, please see the updated comments section.

HTH

Dave J



http://glossopian.co.uk/
"I don't know what I don't know."
Post #1128904
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse