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.
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.
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
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.
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.
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.
15 people are following this question.