Tuesday, May 28, 2024

DB Growth Report



Problem

At some point you are probably going to be asked how quickly your SQL Server database is growing. You could try to create a report based on auto growth's that occurred, but if the data file was initially set large enough no auto growths would be triggered. The T-SQL script in this tip will attempt to give you a pretty good answer to address the growth of the database by looking at backup sizes.

Solution

In this tip I’m going to present you a T-SQL script that presents the data growth for a particular database and its delta compared to the previous month. The script will work only if you have enough historical data in your msdb database, because it makes all the calculations based on the database backup information history.

T-SQL Script

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

Output Of Script 
Note:- This snapshot is for reference which shows only master database data, but the script will gives you user database data

No comments:

Post a Comment

Useful Trace Flags

Trace Flag 1117 (Auto Grow All Files in a Filegroup): This trace flag ensures that all files in a filegroup grow together when one file need...