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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy