1

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

2 Answers 2

3

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
0

The information in the Dashboard is obtained by reading the Default Trace. You can read the default database as explained here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .