question

Vishu-2318 avatar image
0 Votes"
Vishu-2318 asked SeeyaXi-msft answered

Growth Rate

Experts,

Need help to capture the growth rate for multiple database for the last 6-12 months for future projections.

Is there a query for it.

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.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

Need help to capture the growth rate for multiple database

You surely backup all databases and so you can get the required information from the 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




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.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Depends on what monitoring you have set up. If you have not set up any monitoring, there is no data to query. The default trace records autogrowth events, but SQL Server only retains five rollover files of a fairly modest size, so you can count yourself lucky if you can find data three days back. 6-12 months? Just forget it.

If you have a long-term storage of your backups, you could dig around in those.

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.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SeeyaXi-msft edited

Hi @Vishu-2318,

By executing sp_spaceused regularly (such as once a week), you can get rough information, and then do a subtraction to know the growth, but more accurate algorithms require more DMV.
104096-dmv.png
For the growth of the log file, you can check the growth of the log file regularly through DBCC SQLPERF (LOGSPACE).

Note: No matter what DMV it is, it is best to run it regularly to check the data growth.

Best regards,
Seeya


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



dmv.png (12.7 KiB)
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.