February 1, 2010 at 6:08 am
may i know how can we estimate the db growth on daily , weekly and on monthly basis , as i ve lot of user dbs on my server . is any query is there to estimate .
replies are valuable .
February 1, 2010 at 6:23 am
this is usually done by tracking the size of the backup file over time. There are a number of scripts for this on this site alone.
an example
--see growth from backup size in pages.
DECLARE @dbname sysname
SET @dbname = DB_NAME()
SELECT CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format
CONVERT(char, backup_start_date, 108) AS [Time],
@dbname AS [Database Name], [filegroup_name] AS [Filegroup Name],
logical_name AS [Logical Filename],
physical_name AS [Physical Filename],
CONVERT(numeric(9,2),(backed_up_page_count * 8192)/1048576) AS [File Size (MB)],
Growth AS [Growth Percentage (%)]
FROM
(
SELECT b.backup_start_date,
a.backup_set_id,
(a.backed_up_page_count),
a.logical_name,
a.[filegroup_name],
a.physical_name,
(
SELECT CONVERT(numeric(5,2),
(((a.backed_up_page_count*8192) * 100.00) / (i1.backed_up_page_count*8192))-100)
FROM msdb.dbo.backupfile i1
WHERE i1.backup_set_id =
(
SELECT MAX(i2.backup_set_id)
FROM msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
ON i2.backup_set_id = i3.backup_set_id
WHERE i2.backup_set_id < a.backup_set_id AND
i2.file_type='D' AND
i3.database_name = @dbname AND
i2.logical_name = a.logical_name AND
i2.logical_name = i1.logical_name AND
i3.type = 'D'
) AND
i1.file_type = 'D'
) AS Growth
FROM msdb.dbo.backupfile a JOIN msdb.dbo.backupset b
ON a.backup_set_id = b.backup_set_id
WHERE b.database_name = @dbname AND
a.file_type = 'D' AND
b.type = 'D'
) as Derived
WHERE (Growth <> 0.0) OR (Growth IS NULL)
ORDER BY logical_name, [Date]
---------------------------------------------------------------------
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply