question

DevendraSahu-0306 avatar image
0 Votes"
DevendraSahu-0306 asked DevendraSahu-0306 commented

SQL database growth report

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered DevendraSahu-0306 commented

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.
    
 ;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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@OlafHelper-2800
thanks' it working if possible to please share day wise report. and Datafile through not backup history:

0 Votes 0 ·

if possible to please share day wise report

0 Votes 0 ·