question

You'll need a table to store the sizes and record the time you recorded the size. Then schedule a query to record the sizes. How often you run it depends on the level of granularity you want to use for your trend - hourly, daily, weekly etc. I like to track data file size separately from log size, and the scripts below reflect a very simple version of that. You can get quite sophisticated if you need. Create the tables (you won't want yours in tempdb, I just put together a sample you can run from end-to-end that cleans up after itself): USE tempdb GO CREATE TABLE dbo.databaseDataFileSize ( rowId INT IDENTITY(1,1), dbName sysname, dbSize BIGINT, sampleDate DATE, sampleTime TIME) GO CREATE TABLE dbo.databaseLogFileSize ( rowId INT IDENTITY(1,1), dbName sysname, dbSize BIGINT, sampleDate DATE, sampleTime TIME) GO Run a query periodically to populate them: EXEC sp_msforeachdb ' INSERT INTO tempdb.dbo.databaseDataFileSize SELECT ''?'', SUM(size) * 8, cast(getdate() as date), cast(getdate() as time) FROM [?].sys.database_files WHERE type = 0; INSERT INTO tempdb.dbo.databaseLogFileSize SELECT ''?'', SUM(size) * 8, cast(getdate() as date), cast(getdate() as time) FROM [?].sys.database_files WHERE type = 1;' GO Query the table with excel for pretty trend graphs. Or, just run something in SSMS: SELECT * FROM tempdb.dbo.databaseDataFileSize SELECT * FROM tempdb.dbo.databaseLogFileSize -- this cleans up the tables from the sample DROP TABLE tempdb.dbo.databaseDataFileSize DROP TABLE tempdb.dbo.databaseLogFileSize

Thanks Every one....i have tried to make this query from two table in MSDB Database 1 Backupset and other is backupfile 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),file_size/1048576) AS [File Size (MB)], Growth AS [Growth Percentage (%)] FROM ( SELECT b.backup_start_date, a.backup_set_id, a.file_size, a.logical_name, a.[filegroup_name], a.physical_name, ( SELECT CONVERT(numeric(10,2),((a.file_size * 100.00)/i1.file_size)-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] Provide the Database name

Is there a way to pass the dbnames using a variable instead of typing each one at a time?