Analyze SQL Server database historical growth: MONTLY size changes

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