Get Filegrowth info from backuphistory

,

This uses info in backup tables to display growth of datafiles (LDF and MDF) and actual datagrowth (backupsize)  over a period of time.  Replace parameter values with name of your database and date from when you want info displayed.

Results are best viewed in grid.

/***********************************************************
Check growth of .LDF and .MDF from backuphistory.
Lines returned depends on the frequency of full backups
Parameters: database name
            fromdate  (date from which info is requiered in 
                       smalldatetime)
Results best viewed in grid
***********************************************************/
--- Change these vars for your database
declare @dbname varchar(128)
declare @fromdate smalldatetime
select @dbname = 'YourDbName'
select @fromdate = getdate()-30   ---filegrowth last 30 days

create table #sizeinfo
(
filedate datetime null,
dbname nvarchar(128) null,
Dsize numeric (20,0) null,
Lsize numeric (20,0) null,
backup_set_id int null,
backup_size numeric (20,0) null
)

--- tmp pivot table to get mdf en ldf info in one line
insert #sizeinfo
select 
filedate=bs.backup_finish_date,
dbname=bs.database_name, 
SUM(CASE file_type WHEN 'D' THEN file_size ELSE 0 END) as Dsize,
SUM(CASE file_type WHEN 'L' THEN file_size ELSE 0 END) as Lsize,
bs.backup_set_id,
bs.backup_size
from msdb..backupset bs, msdb..backupfile bf
where bf.backup_set_id = bs.backup_set_id
and rtrim(bs.database_name) = rtrim(@dbname)
and bs.type = 'D'
and bs.backup_finish_date >= @fromdate
group by bs.backup_finish_date, bs.backup_set_id, bs.backup_size, bs.database_name
order by bs.backup_finish_date, bs.backup_set_id, bs.backup_size, bs.database_name

select 
Date=filedate, 
Dbname=dbname, 
MDFSizeInMB=(Dsize/1024)/1024, 
LDFSizeInMB=(Lsize/1024)/1024, 
TotalFIleSizeInMB=((Dsize+Lsize)/1024)/1024,
BackupSizeInMB=(backup_size/1024)/1024
from #sizeinfo
order by filedate

drop table #sizeinfo

Rate

5 (2)

Share

Share

Rate

5 (2)