SQL Server: Data file monitor stored procedure

This is a script that lets you monitor datafile sizes in SQL Server. It collects the sizes of all data files in the database and stores them in a history table. You can set this up to run with a SQL Server Agent Job to collect data file sizes nightly.

The table the history gets stored in looks like this:

create table altfiles_history (
date datetime,
dbid smallint,
fileid smallint,
size int
)

The stored procedure code looks like this:

/*
 * size_monitor
 * Timothy Chen Allen
 * http://www.timallen.org/blog
 */
create procedure dbo.size_monitor 
as
set nocount on
	
-- Cursor: names and sizes of data files
declare c_dbsize cursor for
 select dbid, fileid, size
   from master..sysaltfiles

-- Declare variables into which to fetch
declare @dbid smallint
declare @fileid smallint
declare @size int
  
open c_dbsize

fetch next from c_dbsize
 into @dbid, @fileid, @size
 
while @@fetch_status = 0
begin
    fetch next from c_dbsize
     into @dbid, @fileid, @size
    
    insert into trf_dba_utilities..altfiles_history (date, dbid, fileid, size)
    values (getdate(), @dbid, @fileid, @size)
end

return

You can query the history table to see how your data files are growing with this query:

/*
 * 1) size and maxsize are displayed in 8K blocks.  You can
 *    get this to MBs by dividing by 128.
 * 2) maxsize is -1 when the datafile is allowed unlimited
 *    growth.
 */
select s.date,
       d.name database_name, 
       f.name datafile_name,
       s.size/128.0 datafile_size, 
       case when f.maxsize = -1 
            then -1
            else f.maxsize/128.0
       end max_datafile_size
  from altfiles_history s
  left join master.dbo.sysaltfiles f on s.dbid = f.dbid and s.fileid = f.fileid
  left join master.dbo.sysdatabases d on s.dbid = d.dbid
 order by d.name, f.name, s.date

Basically, I'm just storing the size column and keys from SYSALTFILES. This history is a nice thing to have so you can watch file growth and see if you need to be automatically shrinking databases, or to watch for unexpected jumps in size.