Find out how fast is your SQL database growing using the backup metadata from msdb

Sometimes I need to know how fast a database is growing, or which particular database is growing the most out of all the databases on a SQL instance. Now I do not have a central repository where regularly I am polling and storing the database size, among other. Nor do I have a CMDB tool at my current job.

So what I have here is more of an ad-hoc, indirect way to find out database growth using the backup history information from within the MSDB database. This should be used more as an ad-hoc solution though I do find this script very convenient and quick method on occasions when I do need to know.

And, needless to say this script won't help for a database that is not being backed up (there can be a justifiable reason for it) or if you are only keep backup history in msdb for a very short period of time (e.g. purging them after 7 dayts).

THIS IS A SCRIPT SO PLEASE REMEMBER TO MODIFY THE VALUES FOR THE FOLLOWING VARIABLES BEFORE RUNNING IT:

declare @backup_start_date datetime   -- = Example: '7/1/2022'
declare @backup_end_date datetime     -- = Example: '8/1/2022'    -- default is current date/time 
declare @number_of_days int = 30      -- ignored if the @backup_start_date is given

/* GET THE DATABASE SIZE GROWTH INDIRECTLY BY COMPARING CHANGE IN DATABASE BACKUP SZIE
-- You can either specify a date range or number of days to go back to compare with.
   For example you can specify date range if you want to know how much your database grew 
       during a specific period in the past.
   This info can be useful for comparison between two different time periods
   
   And specify number of days if you want to know much the database grew in past week, month, year etc...
-- Uses the backup metadata from the msdb to get the backup size
   If you are like me, you probably keeping the msdb size in check by purging metadata regularly
   So you can only go as far back as you have the backup metadata for.
Limitations:
-- In case of an AG cluster, you must run the query on the replica where the full backups are performed or run it
      as a multi-server query against all nodes simultaneously (recommended).
-- Only looks for the full/complete backups (backup type=D) . In other words, does not consider differential and/or log backups
*/

if OBJECT_ID('tempdb..#db_backup_metadata') is not null drop table #db_backup_metadata
go
-- LETS FIRST GET THE DATA FROM msdb..backupset INTO A TEMP TABLE
declare @backup_start_date datetime   -- = Example: '7/1/2022'
declare @backup_end_date datetime     -- = Example: '8/1/2022'    -- default is current date/time 
declare @number_of_days int = 30      -- ignored if the @backup_start_date is given
if @backup_start_date is null and @number_of_days is null
begin
      raiserror('Error: Either a start date or number of days to go back to must be provided.', 16,1)
  return
end
set @backup_start_date = isnull(@backup_start_date, GETDATE() - @number_of_days)
set @backup_end_date   = isnull(@backup_end_date, GETDATE())
print '-- BEGIN AND START DATES:'
print @backup_start_date
print @backup_end_date
print '--'
if @backup_end_date < @backup_start_date 
begin
      raiserror('Error: Start date cannot be after the end date.', 16,1)
  return
end
SELECT 
bs.backup_set_id,
bs.database_name,
bs.database_creation_date db_create_date,
bs.backup_start_date,
cast(bs.backup_size / 1024/ 1024 /1024 as numeric(19,3)) backup_size_gb
INTO #db_backup_metadata
FROM msdb..backupset bs 
WHERE
bs.type='D' 
and bs.backup_start_date >= @backup_start_date
and bs.backup_finish_date <= @backup_end_date
order by database_name, backup_start_date desc
-- USE THE CTEs TO GET THE DESIRED DATA
;with c1 as
(
select database_name, db_create_date, backup_start_date, backup_size_gb from #db_backup_metadata a
where backup_set_id = 
(select MIN(backup_set_id) from #db_backup_metadata 
where database_name = a.database_name group by database_name)
),
c2 as
(
select database_name, backup_start_date, backup_size_gb from #db_backup_metadata a
where backup_set_id = 
(select MAX(backup_set_id) from #db_backup_metadata 
where database_name = a.database_name group by database_name)
)
select
dbs.name db_name,
c1.db_create_date,
c1.backup_start_date backup_date1, 
c2.backup_start_date backup_date2,
c1.backup_size_gb backup_sze1, 
c2.backup_size_gb backup_size2,
(c1.backup_size_gb - c2.backup_size_gb) * -1 growth_gb,
try_cast(case when c1.backup_size_gb > 0 then
((c1.backup_size_gb - c2.backup_size_gb) * -1) * 100 / c1.backup_size_gb 
else 0 end as numeric(10,2)) growth_rate
from sys.databases dbs
     left join c1 on dbs.name = c1.database_name
 left join c2 on c1.database_name = c2.database_name
where dbs.name !='tempdb'
order by growth_gb desc

Find out how fast is your SQL database growing using the backup metadata from msdb

Rate

Share

Share

Rate