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) 
ROW_NUMBER() OVER(order by backup_start_date) as Row,
cast(backup_size/1024/1024 as decimal(10,2)) Mb 
from msdb..backupset
type='D' and 
database_name=@dbname and
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


Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo di

Stai commentando usando il tuo account Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...