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)
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
zabolab 