SQL database growth report

Devendra Kumar Sahu 216 Reputation points

How to Generate SQL Database Growth Report Day wise report.
SQL Server 2016 SE

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,779 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 28,936 Reputation points

    I guess you backup your databases, and if, you can get the information from backup history:

    -- Transact-SQL script to analyse the database size growth using backup history.
    DECLARE @endDate datetime, @months smallint;
    SET @endDate = GetDate();  -- Include in the statistic all backups from today
    SET @months = 12;           -- back to the last 6 months.
       (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
    SELECT MAIN.DatabaseName
           - (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
    ORDER BY MAIN.DatabaseName

0 additional answers

Sort by: Most helpful