How to Check Monthly Growth of Database in SQL Server (Database Growth :last 6 month database grow)

How to Check Monthly Growth of Database in SQL Server (Database Growth :last 6 month database grow)

**********************************************************

Below is the script I have come up with database growth of database. It will give the last 6 month database growth for all database of particular instance.

DECLARE @endDate datetime, @months smallint;

SET @endDate = GetDate();  -- Include in the statistic all backups from today

SET @months = 6;           -- back to the last 6 months.

;WITH HIST AS

   (SELECT BS.database_name AS DatabaseName

          ,YEAR(BS.backup_start_date) * 100

           + MONTH(BS.backup_start_date) AS YearMonth

          ,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB

          ,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB

          ,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB

    FROM msdb.dbo.backupset as BS

         INNER JOIN

         msdb.dbo.backupfile AS BF

             ON BS.backup_set_id = BF.backup_set_id

    WHERE NOT BS.database_name IN

              ('master', 'msdb', 'model', 'tempdb')

          AND BF.file_type = 'D'

          AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate

    GROUP BY BS.database_name

            ,YEAR(BS.backup_start_date)

            ,MONTH(BS.backup_start_date))

SELECT MAIN.DatabaseName

      ,MAIN.YearMonth

      ,MAIN.MinSizeMB

      ,MAIN.MaxSizeMB

      ,MAIN.AvgSizeMB

      ,MAIN.AvgSizeMB 

       - (SELECT TOP 1 SUB.AvgSizeMB

          FROM HIST AS SUB

          WHERE SUB.DatabaseName = MAIN.DatabaseName

                AND SUB.YearMonth < MAIN.YearMonth

          ORDER BY SUB.YearMonth DESC) AS GrowthMB

FROM HIST AS MAIN

ORDER BY MAIN.DatabaseName

        ,MAIN.YearMonth

--SECTION 1 BEGIN

WITH BackupsSize AS(
SELECT TOP 1000
      rn = ROW_NUMBER() OVER (ORDER BY DATEPART(year,[backup_start_date]) ASC, DATEPART(month,[backup_start_date]) ASC)
    , [Year]  = DATEPART(year,[backup_start_date])
    , [Month] = DATEPART(month,[backup_start_date])
    , [Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([backup_size]/1024/1024/1024),4))
    , [Compressed Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([compressed_backup_size]/1024/1024/1024),4))
FROM 
    msdb.dbo.backupset
WHERE 
    [database_name] = N'XXXX'
AND [type] = 'D'
AND backup_start_date BETWEEN DATEADD(mm, - 13, GETDATE()) AND GETDATE()
GROUP BY 
    [database_name]
    , DATEPART(yyyy,[backup_start_date])
    , DATEPART(mm, [backup_start_date])
ORDER BY [Year],[Month]) 
--SECTION 1 END
 
--SECTION 2 BEGIN
SELECT 
   b.Year,
   b.Month,
   b.[Backup Size GB],
   0 AS deltaNormal,
   b.[Compressed Backup Size GB],
   0 AS deltaCompressed
FROM BackupsSize b
WHERE b.rn = 1
UNION
SELECT 
   b.Year,
   b.Month,
   b.[Backup Size GB],
   b.[Backup Size GB] - d.[Backup Size GB] AS deltaNormal,
   b.[Compressed Backup Size GB],
   b.[Compressed Backup Size GB] - d.[Compressed Backup Size GB] AS deltaCompressed
FROM BackupsSize b
CROSS APPLY (
   SELECT bs.[Backup Size GB],bs.[Compressed Backup Size GB]
   FROM BackupsSize bs
   WHERE bs.rn = b.rn - 1
) AS d
--SECTION 2 END

Method:2