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

• Microsoft SQL Server, versions 6.0 and 6.5

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:

1. 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.

2. 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.

3. Run the script in master.

4. Open Performance Monitor and add the appropriate "SQL User Counter X" from "SQLServer user-defined counters" to the chart.

   drop procedure dbo.sp_user_counterX

/* This procedure returns an integer value representing how full a user

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))

GRANT EXECUTE ON dbo.sp_user_counterX TO probe GO *********Script End************

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. For more information, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q110904
   TITLE     : How to Set Up SQL Performance Monitor Database Alerts