Monitor the Database Growth

Raja P
Raja P used Ask the Experts™
on
How to generate the MS SQL Database growth report with out any third party tool?like any special script /steps to follow?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Build a table with proper schema and schedule a job every day, hour as per your requirement that will take the snapshot of the sizes of the various mdf and ldf files and then you can play with the data collected. You can use the following scripts

1. sp_helpdb

2. SELECT DB_NAME(database_id) AS DatabaseName,
SUM((size*8)/1024) SizeMB
FROM sys.master_files
group by DB_NAME(database_id)
order by DB_NAME(database_id)

3.
SELECT db.name AS [DB Name]
,dbf.[physical_name] AS [File Name]
,dbf.[size] AS [File Size in 8KB]
,db.[create_date] AS [Create Date]
,ss.[Last User Event]
,ss.[Last User Update]
FROM sys.[databases] AS db
LEFT OUTER JOIN sys.[master_files] AS dbf ON [db].[database_id] = [dbf].[database_id]
LEFT OUTER JOIN (SELECT database_id,max(last_user_update) AS [Last User Update], ISNULL(ISNULL(max(last_user_update),max([last_user_seek])),max([last_user_lookup])) AS [Last User Event]
FROM sys.[dm_db_index_usage_stats] GROUP BY database_id) AS ss ON ss.[database_id] = db.[database_id]
ORDER BY db.[name],dbf.[physical_name]

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial