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
This time we take the max size reached during every month to calculate the monthly size change.
This is the report query:
declare @dbname nvarchar(1024) --Configure HERE database name set @dbname ='YourDatabaseName'; --Month Report WITH TempTable(Row,database_name,backup_start_date,Mb) as ( select ROW_NUMBER() OVER(order by left(convert(nvarchar(20),backup_start_date,112),6)) as Row, database_name, left(convert(nvarchar(20),backup_start_date,112),6) month, MAX(cast(backup_size/1024/1024 as decimal(10,2))) Mb from msdb..backupset group by database_name,left(convert(nvarchar(20),backup_start_date,112),6),type having type='D' and database_name=@dbname ) select A.database_name, A.backup_start_date, A.Mb as max_backup_size_mb, A.Mb - B.Mb as delta_mb from TempTable A left join TempTable B on A.Row=B.Row+1 order by database_name,backup_start_date