September 2, 2009 at 6:01 am
this is my situation , ive no previous values to estimate the all dbs growth how can we find the db growth , i know if we find backup history in msdb we can get an idea of the growth , can any one help how to find the backup history of the databases which shows thw page results of the backup details in msdb and how can we estimate the growth by seeing the page results . for ex: my db size is 200 mb
values are appreciated
September 2, 2009 at 8:54 am
This article will help you:
Tracking and Reporting Database Growth
http://www.sqlservercentral.com/articles/Monitoring/66257/%5B/url%5D
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
September 2, 2009 at 10:13 am
I've built a couple of queries using the existing backup history tables in MSDB:
-- Average DB size by month for ALL Databases
select a.database_name,
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' -- 'D' = Full backup
--and a.backup_size > 1073741824 -- > 1 Gig
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
And raw data of backup information
-- 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 /1048576) as decimal (9,2)) as 'Size Meg' ,
cast((a.backup_size /1073741824) as decimal (9,2)) as 'Gig', -- div by 1073741824 to get 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' /*D=Full*/
AND a.backup_start_date > '2008-01-01'
--and a.database_name = 'DB Name'
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply