Tuesday, August 22, 2017

SQL server database growth rate (single DB & more detail)

WITH CTE AS
(
SELECT
DatabaseName = bs.database_name,
backupDate = cast(bs.backup_start_date as date),
DBSizeGB = CONVERT(numeric(10, 1), bf.file_size / 1048576.0 / 1024.0),
--bs.backup_size,
bf.file_size
FROM msdb.dbo.backupset as bs
JOIN msdb.dbo.backupfile as bf ON bs.backup_set_id = bf.backup_set_id
WHERE NOT bs.database_name IN ('master', 'msdb', 'model', 'tempdb')
and bf.file_type = 'D'
and bs.[Type] = 'D'
and bs.database_name in ('ABC')
),
cte_Detail as
(
select
CTE.databaseName,
cte.backupDate,
cte.DBSizeGB,
IncreasedByGB = DBSizeGB - (select top 1 DBSizeGB from cte t2
where t2.backupDate < cte.backupDate
and t2.DatabaseName = cte.DatabaseName
order by t2.backupdate desc),
DaysSince = DateDiff(dd, (select top 1 backupDate from cte t2
where t2.backupDate < cte.backupDate
and t2.DatabaseName = cte.DatabaseName
order by t2.backupdate desc), backupDate)
from CTE
)
select
databaseName,
backupDate,
DBSizeGB,
IncreasedByGB,
DaysSince,
DailyIncrease = cast(round(IncreasedByGB / DaysSince, 2) as decimal(8, 2))
from cte_Detail
order by 2 desc

No comments: