February 10, 2011 at 7:51 am
As i have 150 databases in a server i want to calculate the growth of all database sizes and to find the backup size of every db , can any one help me in this .
instead of not creating a db in he system as it is production one .
February 10, 2011 at 8:07 am
To find backup size for Every DB
select database_name,backup_size,backup_start_date from msdb.dbo.backupset
order by database_name
February 10, 2011 at 8:18 am
Something like this ? This assumes you don't clear your full backup history from msdb. This shows the average backup size per month. You can extrapolate actual database size.
select substring(a.database_name,1,35) as 'Database',
datepart(year,a.backup_start_date) as 'year',
datepart(month,a.backup_start_date) as 'month' ,
avg(cast((a.backup_size /1073741824) as decimal (9,2)))as 'Avg Gig'
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_size > 1073741824 -- > 1 Gig
and a.backup_start_date >= '2009-12-01'
GROUP BY a.database_name,datepart(year,a.backup_start_date),datepart(month,a.backup_start_date)
order by a.database_name,datepart(year,a.backup_start_date) desc,datepart(month,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