Friday, March 23, 2012

Problem with sp_sqlagent_get_perf_counters

I have problem with my production server, it is 4 CPUs and they utilized 100%. And i found this stored procedure EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters taking highest CPU time. And i already turned off all the alerts. And i even killed this stored procedure, but still it is executed by some process, i don't know how it is executing.
KiranShut down the SQLAgent service, and these calls will go away. I am going to guess your 100% CPU problem will remain, however.|||I restarted sql agent, but again it is getting fired by some process.|||If you restarted it, of course, SQL Agent will keep sending these queries to the server. What happens when you leave it shut down?|||I have shutdown the sqlAgent in my server, msdb.dbo.sp_sqlagent_get_perf_counters process was killed by system it self. and now the CPU utilization has reduced from 75% to 50%.
But this solution doesn't work for me because, i have some backup jobs running, so i have to start sqlAgent again. Can you suggest me any other solution how to stop running msdb.dbo.sp_sqlagent_get_perf_counters process without stopping sqlAgent.|||Hi, open the code of this sp.
it's loading all enabled alerts int a temp table and then joining it with sysperfinfo table.

as you said the alerts are off; make sure(select * from msdb.dbo.sysalerts where enabled=1) returns nothing.

run this sp manually and check the execution plan, and check if the cpu spikes when u run it manually.sql

No comments:

Post a Comment