INF: How to Monitor Free Space in a User Database with PerfMon (163036)

SUMMARY

Many database administrators (DBAs) need to know how to schedule alerts indicating that a database is full. SQL Server is tightly integrated with the Windows NT Server Performance Monitor, providing numerous pre-defined counters. One of these counters monitors a database's log percent filled, but none of the counters monitors the database itself. However, you can add this functionality by using the user-defined SQL Server counters.

MORE INFORMATION

To set up user-defined performance counters, do the following:

Grant appropriate permissions for the logon "probe" for the database. On the Manage menu, click Logins. Select "Probe" as the logon name. Place a check in the Permit column for the databases that you want to monitor.
Modify the script below. Replace the "X" in "sp_user_counterX" with a number from 1 to 10 (this procedure only works with 10 databases). Replace "DBNAME" with the name of the user database you want to monitor.
Run the script in master.
Open Performance Monitor and add the appropriate "SQL User Counter X" from "SQLServer user-defined counters" to the chart.

if exists (select * from sysobjects where id =
object_id('dbo.sp_user_counterX') and sysstat & 0xf = 4)
   drop procedure dbo.sp_user_counterX
go

/* This procedure returns an integer value representing how full a user
database is, as a percentage full.
This procedure does not monitor the master, and does not track logs. */ 

CREATE PROCEDURE sp_user_counterX
AS
DECLARE @dbsize dec(15,0)
DECLARE @allocatedpages dec(15,0)

SELECT @dbsize = sum(convert(dec(15),size))
FROM sysusages
WHERE dbid = (SELECT dbid FROM sysdatabases WHERE name = 'DBNAME')
AND (segmap & 4) != 4

SELECT @allocatedpages =
   (SELECT sum(convert(dec(15), reserved))
   FROM  DBNAME..sysindexes         --Note:  No quotes
   WHERE indid in (0,1,255) AND (ID <> 8))

SELECT convert(tinyint,(@allocatedpages / @dbsize) * 100)
GO

GRANT EXECUTE ON dbo.sp_user_counterX TO probe
GO

Use the existing Performance Monitor counters to monitor the log. This procedure does not work for databases that include both data and log segments on the same fragment.