The most simple way to analyze database historical growth is quering database backup catalog.
SQL Server catalog stores informations about every single database backup in msdb..backupset. If you don’t have other instruments to collect historical database size this is a good point to start for a capacity planning. Remembar only this:
– msdb..backupset stores historical informations about backup size NOT database file size. This is good for you if you need to understand how your real stored data are growing day by day… but obviously datafiles are tipically larger: there is empty space inside for future data.
– evey full database backup contains a little part of logs used for recover. For this reason the size reported is not alway exactly your data dimension but usually this is not relevant
In this report you can see the daily changes in your database effective size.
declare @dbname nvarchar(1024) declare @days int --Configure HERE database name set @dbname ='YourDBName' --...and number of days to analyze set @days =365; --Daily Report WITH TempTable(Row,database_name,backup_start_date,Mb) as ( select ROW_NUMBER() OVER(order by backup_start_date) as Row, database_name, backup_start_date, cast(backup_size/1024/1024 as decimal(10,2)) Mb from msdb..backupset where type='D' and database_name=@dbname and backup_start_date>getdate()-@days ) select A.database_name, A.backup_start_date, A.Mb as daily_backup, A.Mb - B.Mb as increment_mb from TempTable A left join TempTable B on A.Row=B.Row+1 order by database_name,backup_start_date