December 28, 2008 at 9:25 am
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
December 28, 2008 at 5:30 pm
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]
December 29, 2008 at 12:34 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply