sql agent -Generic Refresher

• dallas13

  SSCertifiable

  Points: 7491

  More actions

  December 28, 2008 at 9:25 am

  #127753

  i can see these 3 runing in my activity monitor all the times.for sql server 2000(8.0.760)

  SQL Agent-Email Logger

  UPDATE msdb.dbo.sysoperators SET last_email_date = 20081224, last_email_time = 124019 WHERE (id = 5)

  --------------------

  SQL Agent-Generic Refresher

  EXECUTE msdb.dbo.sp_help_alert @alert_id = 14

  -----------------------

  SQL agent-Alert Engine

  EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

  here i can see generic refresher is invoking alert number 14 which generates emails every minutes

  and its a big headache for me

  after my investigation i found this is an alert for number of deadlocks/sec

  then i put traces and profiler, and they told me no deadlocks are there in system

  but when i run select * from sysperfinfo i can see no of deadlocks/sec on database object = 2

  and i run this query after every few minutes and i can see deadlocks=2 means r there

  constantly deadlocks getting generated???? why this counter is stuck on 2?? its not getting reset even though no one is

  connected to server

  means this is a kind of fake alert getting generated every time

  after rebooting my server this happened immediately(when no one is connected to server) and i was geting mails every minutes

  i tried to kill this process manually but it pops up again

  how can i overcome this issue without rebooting my sql server or agent???

  apprecite ur help thanks

• Jonathan Kehayias

  One Orange Chip

  Points: 26778

  More actions

  December 28, 2008 at 5:30 pm

  #918221

  Are you on SP4 for SQL 2000? What is the alert definition that is causing this to fire off?

  Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
  My Blog | Twitter | MVP Profile
  Training | Consulting | Become a SQLskills Insider
  Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

• dallas13

  SSCertifiable

  Points: 7491

  More actions

  December 29, 2008 at 12:34 pm

  #918553

  no i m not on sp-4 but 8.0.760 and we dont want to apply sp-4 due to certain reasons

  SQL Agent-Generic Refresher is executing this

  EXECUTE msdb.dbo.sp_help_alert @alert_id = 14(14= no of deadlocks/sec on datbases object)

  when i select * from sysperfinfo

  where object_name = 'SQLServer:Locks' and

  counter_name = 'Number of Deadlocks/sec' and

  instance_name = 'Database'

  then i can see no of deadlocks =2 every time when i execute this query.

  means the counter is 2 and thats y alerts are there but if sql server

  detects deadlocks automatically and kill low priority process then

  it should be "0"

  but its not setting to "0" i cant catch them in profiler neither i can find them by

  running traces 1204 and 3604

  i dont know how this value got set to 2?? and i cant reset counter

  anyway this is cluster so if i just do manual failover that might solve this issue or i need to r

  reboot my whole windows server?? thnks