Is there anyway I can track the size of my database growth over time for SQL Server 2008? Can I see this from a dashboard somewhere or do I have to script a query to perform this?
Thanks
I think the best way to track size growth on a database is to collect weekly or as frequent as you want their sizes and insert into a static table from another SQL instance, different than the one of the database monitored.
You can use the query below that returns the data and log file sizes in MegaBytes and inserts the data into another tempdb table. After one month or year you will be able to check the growth by analyzing the output from that table and even create a graph.
CREATE TABLE tempdb..DB_size_growth
(dbname NVARCHAR(256),
mb_data_file NUMERIC(12,2),
data_file_nr INT ,
mb_log_file NUMERIC(12,2),
log_file_nr INT)
DECLARE @dbname AS NVARCHAR(3000)
DECLARE @exec AS NVARCHAR(3999)
DECLARE DB_NAME CURSOR FOR
SELECT name FROM master.dbo.sysdatabases where has_dbaccess(name) = 1
OPEN DB_NAME
FETCH NEXT FROM DB_NAME INTO @dbname
TRUNCATE TABLE tempdb..DB_size_growth
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec = 'INSERT INTO tempdb..DB_size_growth (dbname, mb_data_file, data_file_nr, mb_log_file, log_file_nr)
SELECT b.name AS dbname
,(SELECT convert(numeric(12,3),convert(numeric(12,2),((sum(a1.size))*8))/1024)
from ' + QUOTENAME(@dbname, '[') + '.dbo.sysfiles as a1 where a1.groupid <> 0 ) as mb_data_file
,(SELECT count(a1.size) from ' + QUOTENAME(@dbname, '[') + '.dbo.sysfiles as a1 where a1.groupid <> 0 ) as data_file_nr
,(SELECT convert(numeric(12,3),convert(numeric(12,2),((sum(a.size))*8))/1024)
from ' + QUOTENAME(@dbname, '[') + '.dbo.sysfiles as a where a.groupid = 0 ) as mb_log_file
,(SELECT count(a.size) from ' + QUOTENAME(@dbname, '[') + '.dbo.sysfiles as a where a.groupid = 0 ) as log_file_nr
FROM master.dbo.sysdatabases as b WHERE name = '''+ @dbname +'''
group by b.name'
EXEC (@exec)
FETCH NEXT FROM DB_NAME INTO @dbname
END
CLOSE DB_NAME
DEALLOCATE DB_NAME
select * from tempdb..DB_size_growth
The information in the Dashboard is obtained by reading the Default Trace. You can read the default database as explained here