SQLServerCentral / Article Discussions / Discuss content posted by Brandie Tarvin / Article Discussions by Author / Monitor Database Growth / Latest Posts

RE: Monitor Database Growth

David Jackson — Tue, 21 Jun 2011 05:34:10 GMT

[quote][b]raj-211912 (6/20/2011)[/b][hr]even it was not working for me too. as it is showing 0 mb growth rate.[/quote]I've amended my version, please see the updated comments section.HTHDave J

RE: Monitor Database Growth

chinni1978 — Mon, 20 Jun 2011 12:10:47 GMT

even it was not working for me too. as it is showing 0 mb growth rate.

RE: Monitor Database Growth

David Jackson — Mon, 13 Jun 2011 06:28:41 GMT

Found this thread and thought I'd post my version for anyone still following itHTHDave J[code="sql"]/****** Object: StoredProcedure [dbo].[usp_Check_DB_growth] Script Date: 06/14/2011 10:14:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER 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[/code]

RE: Monitor Database Growth

ericvigil — Fri, 11 Sep 2009 16:38:32 GMT

I will let you know... thanks...

RE: Monitor Database Growth

Jay Rajgor — Tue, 25 Aug 2009 12:51:06 GMT

...Here is the 2000 Version..of the code....--PART 1If exists (Select name from sysobjects where name = 'DBGrowthRate' and Type = 'U') Drop Table dbo.DBGrowthRateCreate 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)Select sd.name as DBName, mf.name as FileName, mf.dbid, fileid, sizeinto #TempDBSizefrom master.dbo.sysdatabases sdjoin master.dbo.sysaltfiles mfon sd.dbid = mf.dbidOrder by mf.dbid, sd.nameInsert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)(Select tds.DBName, tds.dbid, Sum(tds.Size) as NumPages, Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,0 as GrowthAmt, GetDate() as MetricDatefrom #TempDBSize tdswhere tds.dbid not in (Select Distinct DBID from DBGrowthRate where DBName = tds.dbid)Group by tds.dbid, tds.DBName)Drop table #TempDBSizeSelect *from DBGrowthRate--Above creates initial table and checks initial data--PART 2--Below is the code run weekly to check the growth.Select sd.name as DBName, mf.name as FileName, mf.dbid, fileid, sizeinto #TempDBSize2from master.dbo.sysdatabases sdjoin master.dbo.sysaltfiles mfon sd.dbid = mf.dbidOrder by mf.dbid, sd.nameIf 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.dbid, Sum(tds.Size) as NumPages, dgr.CurSize as OrigSize, Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize, ((Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))) - dgr.CurSize )as GrowthAmt, GetDate() as MetricDate from #TempDBSize2 tds join DBGrowthRate dgr on tds.dbid = dgr.DBID Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate where DBID = dgr.DBID) Group by tds.dbid, tds.DBName,dgr.CurSize, dgr.OrigSize) End --Select *--from DBGrowthRate----Verifies values were enteredDrop table #TempDBSize2

RE: Monitor Database Growth

Jay Rajgor — Tue, 25 Aug 2009 12:37:18 GMT

All...I have corrected the code..As it has flaws dealing with date comparison...And hence you are not getting the GROWTH amount on weekly runs...Here is the UPDATED code (works for 2005, 2008)....Also, I have converted the GrowthAMT to Numeric (instead of VARCHAR), --- Allowing you to export the results in EXCEL for further number crunchingLet me know if this works for your environments.--PART 1 (UPDATED, Jignesh Rajgor 08/25/2009)If exists (Select name from sys.objects where name = 'DBGrowthRate' and Type = 'U') Drop Table dbo.DBGrowthRateCreate 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)Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, sizeinto #TempDBSizefrom sys.databases sdjoin sys.master_files mfon sd.database_ID = mf.database_IDOrder by mf.database_id, sd.nameInsert 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)) * 8000)/1024)/1024)) as OrigSize,Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,0 as GrowthAmt, GetDate() as MetricDatefrom #TempDBSize tdswhere tds.database_ID not in (Select Distinct DBID from DBGrowthRate where DBName = tds.database_ID)Group by tds.database_ID, tds.DBName)Drop table #TempDBSizeSelect *from DBGrowthRate--Above creates initial table and checks initial data--PART 2--Below is the code run weekly to check the growth.Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, sizeinto #TempDBSize2from sys.databases sdjoin sys.master_files mfon sd.database_ID = mf.database_IDOrder by mf.database_id, sd.nameIf 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)) * 8000)/1024)/1024)) as CurSize, ((Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))) - 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) End --Select *--from DBGrowthRate----Verifies values were enteredDrop table #TempDBSize2

RE: Monitor Database Growth

sunshine-587009 — Tue, 07 Apr 2009 15:44:16 GMT

How do you get it to insert rather than update? I want to keep track of the changes in size weekly...

RE: Monitor Database Growth

Jay Rajgor — Wed, 18 Mar 2009 12:46:24 GMT

All SQL Folks...The original script has MAJOR error....--In PART2 --- where it runs to update the growth table...it tries to convert the DATE to CHAR...and then compares with GetDATE() -- which is not correct.--- I have corrected the script...BELOW>..replace the script code with Following for your EXISTS condition....This works good for SQL 2000....Change your EXISTS condition accordingly for 2005 script too..-PART 2 -- CORRECTED EXISTS Section below...Replace on original script.;-)......If Exists (Select Distinct DBName from #TempDBSize2 where DBName in (Select Distinct DBName from DBGrowthRate)) and GetDate() > (Select Distinct Max(MetricDate) from DBGrowthRate)

RE: Monitor Database Growth

jamirphillips-790261 — Tue, 11 Nov 2008 20:17:30 GMT

Worked great for me. Especially w/ the recommended changes for 2000/2005Thanks a bunch....JP

RE: Monitor Database Growth

lrhyrne — Wed, 30 Jul 2008 09:13:03 GMT

The original script worked for me. I had to, of course, change the size of my database; so I delete a significant amount of history, did a full backup and ran the script again.Everything worked fine from what I could tell."One shalt thou not count, neither count thou two, only then proceeding to three."

RE: Monitor Database Growth

jaye.merritt.ctr — Tue, 29 Jul 2008 14:43:48 GMT

for SQL 2000 by changing sys.Master_Files to sysaltfiles and sys.databases to sysdatabases. Make sure to change your column names appropriately if you make this alteration!What do you mean by Make sure to change your column names appropriately if you make this alteration!Sorry new at this

RE: Monitor Database Growth

jsql12 — Mon, 30 Jun 2008 12:12:29 GMT

I modified the script to do what I want and it is working well for me. This is for SQL 2000; you can get it to work with SQL 2005 with a minor tweak of the sys tables.--Part1CREATE TABLE [DBGrowthRate] ( [DBGrowthID] [int] IDENTITY (1, 1) NOT NULL , [DBName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DBID] [int] NULL , [OrigSize] [decimal](10, 2) NULL , [CurSize] [decimal](10, 2) NULL , [GrowthAmt] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [InitialDate] [datetime] NULL , [ReportDate] [datetime] NULL CONSTRAINT [DF_ReportDate] DEFAULT (getdate())) ON [PRIMARY]GO--PART 2--Below is the code run weekly to check the growth.create Proc usp_DBGrowthRateASSelect sd.name as DBName, af.dbid, Sum(af.Size) as NumPages, Convert(decimal(10,2),(Sum(Convert(decimal(10,2),af.Size)) * 8)/1024) as CurSizeinto #TempDBSizefrom master..sysdatabases sd join master..sysaltfiles afon sd.dbid = af.dbidGroup by af.dbid, sd.nameOrder by sd.name, af.dbidIf Exists (Select Distinct DBName from #TempDBSize where DBName in (Select Distinct DBName from DBGrowthRate)) and Convert(varchar(10),GetDate(),101) > (Select Distinct Convert(varchar(10),Max(InitialDate),101) as InitialDate from DBGrowthRate) Begin update dgr set dgr.CurSize = tds.Cursize from DBGrowthRate dgr inner join #TempDBSize tds on tds.DBID = dgr.DBID update dgr set dgr.GrowthAmt = tds.CurSize - dgr.OrigSize from DBGrowthRate dgr inner join #TempDBSize tds on tds.DBID = dgr.DBID update DBGrowthRate set ReportDate = GetDate() EndElse IF Exists (Select Distinct DBName from #TempDBSize where DBName not in (Select Distinct DBName from DBGrowthRate)) Begin Insert into dbo.DBGrowthRate (DBName, DBID,OrigSize, CurSize, GrowthAmt, InitialDate) (Select tds.DBName, tds.dbid, tds.CurSize, tds.CurSize, '0.00 MB', GetDate() from #TempDBSize tds where tds.dbid not in (Select Distinct DBID from DBGrowthRate dgr where dgr.DBID = tds.DBID) ) EndDROP TABLE #TempDBSizeGO

RE: Monitor Database Growth

F. van Ruyven — Thu, 22 May 2008 03:05:03 GMT

I think the second part of part 2 is in error; I think it should be like this:--ElseIF Exists (Select t.DBName, g.DBName from #TempDBSize2 tleft outer join DBGrowthRate g on T.dbname = g.DBName where g.dbname is null) Begin 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)) * 8000)/1024)/1024)) as OrigSize, Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize, '0.00 MB' as GrowthAmt, GetDate() as MetricDate from #TempDBSize2 tds where tds.database_ID not in (Select Distinct DBID from DBGrowthRate where DBName = tds.DBName) Group by tds.database_ID, tds.DBName) EndThis will do the trick for a newly added database.

RE: Monitor Database Growth

David Masterson — Thu, 10 Apr 2008 19:59:35 GMT

Has anyone managed to get this script to run correctly?

RE: Monitor Database Growth

Dugi — Fri, 28 Mar 2008 06:30:01 GMT

why the discussion is offline, since we are asking about any updates on this script!plz tell us someone ( do you find any alternative solution or not ?!)thnx!:w00t:

RE: Monitor Database Growth

DBA-640728 — Thu, 13 Mar 2008 13:09:31 GMT

Has anyone been using this script and has been working for them?

RE: Monitor Database Growth

DBA-640728 — Thu, 13 Mar 2008 13:05:32 GMT

hi for some reason i always get the growth size to be 0, do you have an updated script?

RE: Monitor Database Growth

Aseda — Mon, 04 Feb 2008 14:30:12 GMT

That is how I have it.

RE: Monitor Database Growth

drama1908 — Mon, 04 Feb 2008 10:30:02 GMT

For SQL 2000 in part two, you need to make the following changes:1. Change all references database_id to dbid2. Change all references to file_id to fileid3. Change all references to sys.master_files to master.dbo.sysaltfiles4. Change all references to sys.databases to master.dbo.sysdatabases

RE: Monitor Database Growth

Aseda — Mon, 14 Jan 2008 14:39:33 GMT

Can someone tell me why this script always gives the growth amount as 0 even when the origsize and cursize are significantly different.

RE: Monitor Database Growth

itmastera — Thu, 29 Nov 2007 03:55:02 GMT

I think that script have a little mistake. Field size in table sys.master_files contains current filesize in 8-KB pages. So, this part of script is not right:Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024. Author wants to get file size in MB, but it must be like this: Sum(Convert(decimal(10,2),tds.Size)) * 8192)/1024)/1024. But the easiest way to get file size in MB is to write this: Sum(Convert(decimal(10,2),tds.Size)) * 8/1024

RE: Monitor Database Growth

Sunil Telikicharla-457463 — Fri, 19 Oct 2007 04:08:05 GMT

The script looks very handy. But i have a small doubt. I am getting the orig size and current size as the same values and as such growth rate is coming up as 0.Can you guide me on this.

Monitor Database Growth

Brandie Tarvin — Sun, 23 Sep 2007 11:38:00 GMT

Comments posted to this topic are about the item [B]Monitor Database Growth[/B]