/****** 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