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
About Paolo Zaboi 44 Articles
Fermamente convinto che "I predatori dell'Arca Perduta" sia uno dei migliori film della storia del cinema, vive e passa il suo tempo in quel di Milano. Adora i film commercialissimi, tutto ciò che è Marvel, le vecchie avventure grafiche della Lucas e le serie TV. Giocatore (nel poco tempo libero) su Pc e Playstation, con fiducia e speranza, attende dagli anni '80 l'uscita della prossima console Atari. Ha creato questo blog un po' per spirito di condivisione... è un po' per scrivere in libertà di tutto quello che gli piace. Odia leggere i lunghi profili sui blog per questo il resto lo trovate solo su Linkedin: https://www.linkedin.com/in/paolozaboi ------ Firmly convinced that "Riders of the Lost Ark" is one of the best movies in cinema history, he lives and spend all his time in Milan. He loves movies, everything Marvel, Lucas's old graphic adventures and TV series. Player (in leisure time) on PC and Playstation, with confidence and hope, expects the release of the next Atari console from the 1980's. He created this blog (maybe...) for sharing spirit ... or simply to write free about anything he loves. He hates reading long profiles on blogs so all the rest is on Linkedin: https://www.linkedin.com/in/paolozaboi

Be the first to comment

Leave a Reply

Your email address will not be published.


*