Instance level Database Growth Report

Instance level 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

        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

        MONTH(BS.backup_start_date)

),

PIVOTED AS

(

    SELECT

        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

            Month,

            AvgSizeMB

        FROM HIST

        ) AS SourceTable

    PIVOT

    (

        SUM(AvgSizeMB)

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

    ) AS PivotTable

),

EarliestMonth AS

(

    SELECT

        CASE

            WHEN January > 0 THEN 1

            WHEN February > 0 THEN 2

            WHEN March > 0 THEN 3

            WHEN April > 0 THEN 4

            WHEN May > 0 THEN 5

            WHEN June > 0 THEN 6

            WHEN July > 0 THEN 7

            WHEN August > 0 THEN 8

            WHEN September > 0 THEN 9

            WHEN October > 0 THEN 10

            WHEN November > 0 THEN 11

            WHEN December > 0 THEN 12

            ELSE NULL

        END AS EarliestAvailableMonth,

        CASE

            WHEN January > 0 THEN January

            WHEN February > 0 THEN February

            WHEN March > 0 THEN March

            WHEN April > 0 THEN April

            WHEN May > 0 THEN May

            WHEN June > 0 THEN June

            WHEN July > 0 THEN July

            WHEN August > 0 THEN August

            WHEN September > 0 THEN September

            WHEN October > 0 THEN October

            WHEN November > 0 THEN November

            WHEN December > 0 THEN December

            ELSE NULL

        END AS EarliestMonthSize

    FROM

        PIVOTED

),

CurrentMonthData AS

(

    SELECT

        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 AS CurrentMonthSize

    FROM

        PIVOTED

),

Growth AS

(

    SELECT

        CASE

            WHEN EarliestMonthSize > 0 THEN FORMAT(

                ((CurrentMonthSize - EarliestMonthSize) / NULLIF(EarliestMonthSize, 0)) * 100, '0.00')

            ELSE 'N/A'

        END AS GrowthPercentage

    FROM

        CurrentMonthData

    CROSS JOIN

        EarliestMonth

)

SELECT

    @@SERVERNAME AS ServerName,

    January,

    February,

    March,

    April,

    May,

    June,

    July,

    August,

    September,

    October,

    November,

    December,

    GrowthPercentage

FROM

    PIVOTED

CROSS JOIN

    Growth

ORDER BY

    ServerName;