SET NOCOUNT ON
/*
Author:    Nicholas Williams
Date:  3rd February 2008
Desc:  Calculates Growth Info for all databases on a server that are being backed up. Relies on the backup tables, and as a result will only contain as many
       days history as do the backup tables(@iNoSamples). If a database is not being backup up the results will be NULL. (For example the Tempdb)
       This is a rewrite of something I did a few years ago, as I dont know where I saved the other code. bummer.
Email: Nicholas.Williams@reagola.com      

Tested On:
       SQL 2000
       SQL 2005
       SQL 2008
       SQL 2008R2      
*/

CREATE TABLE ##tbl_DataSize
       (
       Size    DECIMAL(20)
       )

CREATE TABLE #tbl_GrowthData
       (
        DatabaseName        VARCHAR(50)
       ,NoSampleDays       DECIMAL(20,3)
       ,DataSizeMB         DECIMAL(20,3)
       ,LogSizeMB          DECIMAL(20,3)
       ,BackupSizeMB       DECIMAL(20,3)
       ,TotalSpaceMB       DECIMAL(20,3)
       ,DataGrowth         DECIMAL(20,3)
       ,LogGrowth          DECIMAL(20,3)
       ,GrowthPercentage   DECIMAL(20,3)
       )

DECLARE
        @iNoSamples     INT
       ,@nMaxBackupSize    DECIMAL
       ,@nMinBackupSize    DECIMAL
       ,@nMaxLogSize       DECIMAL
       ,@nMinLogSize       DECIMAL
       ,@nMaxDataSize      DECIMAL
       ,@nMinDataSize      DECIMAL
       ,@vcDatabaseName    VARCHAR(50)
       ,@dtMaxBackupTime   DATETIME
       ,@dtMinBackupTime   DATETIME
       ,@iMinBackupID      INT
       ,@iMaxBackupID      INT
  
DECLARE file_cursor CURSOR FOR
SELECT [name] FROM master.dbo.sysdatabases
ORDER BY [name]
OPEN file_cursor

FETCH NEXT FROM file_cursor
INTO @vcDatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN  

   SET @dtMaxBackupTime =
       (
           SELECT MAX(backup_finish_date)
           FROM msdb.dbo.backupset
           WHERE database_name = @vcDatabaseName
           AND [type] = 'D'
       )
                          
   SET @dtMinBackupTime =
       (
           SELECT MIN(backup_finish_date)
           FROM msdb.dbo.backupset
           WHERE database_name = @vcDatabaseName
           AND [type] = 'D'
       )
                          
   SET @iNoSamples =
       DATEDIFF
           (
             dd
            ,@dtMinBackupTime
            ,@dtMaxBackupTime
           )

   SET @nMaxBackupSize  =  
       (
           SELECT backup_size
           FROM msdb.dbo.backupset
           WHERE database_name = @vcDatabaseName
           AND [type] = 'D'
           AND backup_finish_date = @dtMaxBackupTime
       )
                          
   SET @nMinBackupSize  =  
       (
           SELECT backup_size
           FROM msdb.dbo.backupset
           WHERE database_name = @vcDatabaseName
           AND [type] = 'D'
           AND backup_finish_date = @dtMinBackupTime
       )

   SET @iMaxBackupID =      
       (
           SELECT MAX(backup_set_id)
           FROM msdb.dbo.backupset
           WHERE database_name = @vcDatabaseName
           AND [type] = 'D'
           AND backup_finish_date = @dtMaxBackupTime
       )
                          
   SET @iMinBackupID =      
       (
           SELECT MAX(backup_set_id)
           FROM msdb.dbo.backupset
           WHERE database_name = @vcDatabaseName
           AND [type] = 'D'
           AND backup_finish_date = @dtMinBackupTime
       )

   SET @nMaxLogSize =        
       (
           SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) /  1048576)
           FROM msdb.dbo.backupfile
           WHERE backup_set_id = @iMaxBackupID
           AND file_type = 'L'
       )
                          
   SET @nMinLogSize =        
       (
           SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) /  1048576)
           FROM msdb.dbo.backupfile
           WHERE backup_set_id = @iMinBackupID
           AND file_type = 'L'
       )
                          
   SET @nMaxDataSize =      
       (
           SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) /  1048576)
           FROM msdb.dbo.backupfile
           WHERE backup_set_id = @iMaxBackupID
           AND file_type = 'D'
       )
                          
   SET @nMinDataSize =      
       (
           SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) /  1048576)
           FROM msdb.dbo.backupfile
           WHERE backup_set_id = @iMinBackupID
           AND file_type = 'D'
       )

   EXEC ('INSERT INTO ##tbl_DataSize
   SELECT CAST((SUM(size)) as DECIMAL(20,3)) FROM '+@vcDatabaseName+'.dbo.sysfiles')

   INSERT INTO #tbl_GrowthData
   SELECT
        @vcDatabaseName DatabaseName
       ,@iNoSamples NoSampleDays
       ,@nMaxDataSize
       ,@nMaxLogSize
       ,@nMaxBackupSize / 1048576
       ,((size * 8192) / 1048576) TotalSpaceUsed  
       ,@nMaxDataSize - @nMinDataSize
       ,@nMaxLoggSize  - @nMinLogSize
       ,(((@nMaxDataSize + @nMaxLogSize) - (@nMinDataSize+ @nMinLogSize)) / (@nMinDataSize+ @nMinLogSize)) * 100.00
       --growth percentage is calculated based upon the original data size, before the growth.
       --as a result it may look a little funny, but it is accurate. or at least I think so :)
   FROM ##tbl_DataSize

   TRUNCATE TABLE ##tbl_DataSize

   FETCH NEXT FROM file_cursor
   INTO @vcDatabaseName

END
CLOSE file_cursor
DEALLOCATE file_cursor

SELECT     *
FROM #tbl_GrowthData

DROP TABLE ##tbl_DataSize
DROP TABLE #tbl_GrowthData

SET NOCOUNT OFF