User Settable Perf Counters NOT triggering alerts 
Author Message
 User Settable Perf Counters NOT triggering alerts

I'm trying to set up an alert that notifies me when there are 2 or more
blocked processes.

I've created a stored proc that basically checks master.dbo.sysprocesses
where blocked != 0, which then sets the first user setting perf counter
using sp_user_counter1. I fire this proc every minute using SQLAgent and it
works fine - I can monitor the counter using performance monitor (or system
monitor for those of us playing with Win2K now).

I then created another stored proc that returns information about blocked
and blocking processes (including a dbcc inputbuffer(spid)). I have a
SQLAgent job that executes this proc and emails me the results (using
xp_sendmail). This all works fine too.

However, when I tell SQLAgent to associate an alert on the performance
counter with the job (i.e. to fire the job as a response to the alert)
things start to go awry. Basically, the alert never fires, even though I
monitor the performance counter and can clearly see that the conditions of
the alert (i.e. that the perf counter > 1) have been met.

By running profiler, I could see SQLAgent probing the server every 20
seconds checking the performance counters (with
msdb.dbo.sp_sqlagent_get_per_counter) and when I run that proc myself I can
see that the first user settable counter is greater than 1 (by jamming up
the processes with a dbcc dbreindex on a big table for several (>10)
minutes). However, the alert never fires.

I've checked all the alert settings and they all look fine. I checked the
row in msdb.dbo.sysalerts and compared it to the others that fire OK and
there was nothing I could see that was different (apart from the obvious
name, last_occurrence_date, etc., of course). I've tried creating an alert
on the perf counter with no response associated with it but its occurrence
count never increases. I've stopped and restarted SQLAgent & MSSQLServer,
but this doesn't seem to make a difference.

This is all running on SQL2000 on a Win2K AS cluster. Any suggestions would
be welcome as this is getting rather frustrating. Is anybody else getting
enabled perf counter alerts that don't fire when their condition is true?

Mike Hodgson
Technical Support Specialist (DBA)
Mallesons Stephen Jaques
Ph:  +61 2 9296 3668
Fax: +61 2 9296 3999

Sat, 15 Nov 2003 11:21:28 GMT
 [ 1 post ] 

 Relevant Pages 

1. User-Settable Alerts

2. Perfmon alerts and user defined counters not working sometimes

3. Where did all my counters go - NT-Perf Counters lost after SQL-Server installation

4. perf counters on sql 7 clusters

5. Sequel server Perf Counters??

6. missing instances in perf counter SQLServer:Replication-Dist

7. Non-admin acces to MSSQL perf. counters

8. Sub-par performance but perf counters don't tell why

9. No SQL Perf Monitor Counters

10. How to add SQL Perf Counters to Perfmon

Powered by phpBB® Forum Software