Analyze SQL Server database historical growth: DAILY 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. 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.

tableresults

 

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

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.


*