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

• Microsoft SQL Server versions 7.0, 6.0, 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.

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 

Q110904 : How to Set Up SQL Performance Monitor Database Alerts

Additional query words: sql60 sql65 1105

Keywords : kbusage SSrvAdmin SSrvMon
Version : winnt:6.0,6.5,7.0
Platform : winnt
Issue type : kbinfo