September 7, 2005 at 10:10 am
How To capture File Growth for each user DB?
Jagan
September 7, 2005 at 10:20 am
September 7, 2005 at 2:52 pm
Assuming you backup your databases daily (or at least regularly), then the database size history is stored in the backup tables. Here's a script I wrote to look at backup statistics, and it doubles as my database growth chart. No need to create & maintain any extra tables. (we back up all DBs daily)
-- Display ALL backup info w/ elapsed time ... ORDER BY DATABASE, DATE
SELECT a.server_name as 'Server',
a.database_name as 'Database',
convert(varchar(25),a.backup_start_date,100) AS 'Start Date',
convert(varchar(25),a.backup_finish_date,100) AS 'Finish Date',
DATENAME(weekday, a.backup_finish_date) AS 'Day' ,
datediff(minute, a.backup_start_date, a.backup_finish_date) as 'Mins' ,
cast(cast(datediff(minute, a.backup_start_date, a.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours' ,
case
when datediff(minute, a.backup_start_date, a.backup_finish_date) > 0
then cast(ceiling(a.backup_size /1048576) / datediff(minute, a.backup_start_date, a.backup_finish_date) as decimal (8,1))
else 0
end as 'Meg/Min',
ceiling(a.backup_size /1048576) as 'Size Meg' , cast((a.backup_size /1073741824) as decimal (9,2)) as 'Gig', a.user_name,a.backup_size as 'Raw Size'
FROM msdb.dbo.backupset a
join msdb.dbo.backupset b on a.server_name = b.server_name and a.database_name = b.database_name
WHERE a.type = 'D' and b.type = 'D' -- AND a.backup_start_date > '2005-01-01' -- Optional data range
group by a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name
order by a.server_name, a.database_name, a.backup_start_date desc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply