Thursday, March 9, 2006

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
create procedure dbo.size_monitor
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
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)


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, database_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,,

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.

No comments:

Post a Comment

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.