Database Growth Report

 Database Growth Report

DECLARE @endDate DATETIME, @startDate DATETIME, @currentMonth INT;

SET @endDate = GETDATE();  -- Current date

SET @startDate = DATEFROMPARTS(YEAR(@endDate), 1, 1);  -- Start of the current year

SET @currentMonth = DATEPART(MM, @endDate);  -- Get the current month

 

;WITH HIST AS

   (SELECT

        BS.database_name AS DatabaseName,

        MONTH(BS.backup_start_date) AS Month,

        CONVERT(NUMERIC(10, 2), 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

        BS.database_name NOT IN ('master', 'msdb', 'model', 'tempdb')

        AND BF.file_type = 'D'

        AND BS.backup_start_date BETWEEN @startDate AND @endDate

    GROUP BY

        BS.database_name,

        MONTH(BS.backup_start_date)

   ),

   PIVOTED AS

   (

       SELECT

           DatabaseName,

           ISNULL([1], 0) AS January,

           ISNULL([2], 0) AS February,

           ISNULL([3], 0) AS March,

           ISNULL([4], 0) AS April,

           ISNULL([5], 0) AS May,

           ISNULL([6], 0) AS June,

           ISNULL([7], 0) AS July,

           ISNULL([8], 0) AS August,

           ISNULL([9], 0) AS September,

           ISNULL([10], 0) AS October,

           ISNULL([11], 0) AS November,

           ISNULL([12], 0) AS December

       FROM

           (SELECT

                DatabaseName,

                Month,

                AvgSizeMB

            FROM HIST

           ) AS SourceTable

       PIVOT

       (

           MAX(AvgSizeMB)

           FOR Month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])

       ) AS PivotTable

   ),

   FirstMonthData AS

   (

       SELECT

           DatabaseName,

           COALESCE(NULLIF(January, 0), NULLIF(February, 0), NULLIF(March, 0),

                    NULLIF(April, 0), NULLIF(May, 0), NULLIF(June, 0),

                    NULLIF(July, 0), NULLIF(August, 0), NULLIF(September, 0),

                    NULLIF(October, 0), NULLIF(November, 0), NULLIF(December, 0))

           AS FirstMonthValue,

           ISNULL(NULLIF(January, 0),

           ISNULL(NULLIF(February, 0),

           ISNULL(NULLIF(March, 0),

           ISNULL(NULLIF(April, 0),

           ISNULL(NULLIF(May, 0),

           ISNULL(NULLIF(June, 0),

           ISNULL(NULLIF(July, 0),

           ISNULL(NULLIF(August, 0),

           ISNULL(NULLIF(September, 0),

           ISNULL(NULLIF(October, 0),

           ISNULL(NULLIF(November, 0), December))))))))))) AS FirstMonth

       FROM PIVOTED

   )

SELECT

    PIVOTED.DatabaseName,

    January,

    February,

    March,

    April,

    May,

    June,

    July,

    August,

    September,

    October,

    November,

    December,

    CASE

        WHEN FirstMonthValue = 0 THEN NULL

        ELSE FORMAT(

            ((ISNULL(CASE @currentMonth

                        WHEN 1 THEN January

                        WHEN 2 THEN February

                        WHEN 3 THEN March

                        WHEN 4 THEN April

                        WHEN 5 THEN May

                        WHEN 6 THEN June

                        WHEN 7 THEN July

                        WHEN 8 THEN August

                        WHEN 9 THEN September

                        WHEN 10 THEN October

                        WHEN 11 THEN November

                        WHEN 12 THEN December

                     END, 0) - FirstMonthValue) / NULLIF(FirstMonthValue, 0)) * 100, '0.00')

    END AS GrowthPercentage

FROM

    PIVOTED

    INNER JOIN FirstMonthData ON PIVOTED.DatabaseName = FirstMonthData.DatabaseName

ORDER BY

    PIVOTED.DatabaseName;