Microsoft KB Archive/163036

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

Article ID: 163036

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q163036



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

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.

Additional query words: sql60 sql65 1105

Keywords: kbinfo kbusage KB163036

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.