May 2, 2002 at 9:45 am
I am receiving the error msg every 20 seconds on one sql server:
[298] SQLServer Error: 8649, The query has been canceled because the estimated cost of this query (10967053) exceeds the configured threshold of 1100000. Contact the system administrator. [SQLSTATE 42000].
The "query" causing this error appear to be the system stored proc sp_sqlagent_get_perf_counters. The error is only occuring on one of 28 sql servers, all configured identically - mssql2000 sp2 enterprise edition, win2k advanced server, 4GB RAM, 4 Processors, running in a 2-node active/active cluster. Although I have stopped the error by raising the query governor limit to 11,000,000, I cannot figure out why the cost estimate to run this stored proc is so high. Might it have something to do with the auto_create_statistics and auto_update_statistics settings on the databases on that server? Some other server option?
Any help would be greatly appreciated.
Justin Randall
Ceridian Hosting Services
Atlanta, GA
May 2, 2002 at 10:38 am
Have you checked the SQL Server Logs and Event Viewer to see if any details?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 10:57 am
Do you have a lot of alerts set up or are the alerts different between your servers?
May 2, 2002 at 11:24 am
The SQL Server Logs and Win2K Event Logs have no information regarding this error. It only shows up in the SQL Agent error log, with no additional information.
May 2, 2002 at 11:28 am
The only alerts we have set up are tied to jobs that are run from a single administrative sql server and distributed to all the production servers. They are essentially the same across the environment.
May 2, 2002 at 12:26 pm
Hey Justin,
Maybe extract the proc and try running it in query analyzer to see the query plan, and show server trace...? Just set @all_counters = 0 and run it. Run it on a box that works fine and then on the problem one and see if there are any differences with the query plan or server trace...
Not sure if that would help or not...
JOHN
John Aspenleiter
John Aspenleiter
May 2, 2002 at 12:59 pm
The sp is the code that checks for condition alerts to notify you about. You may have the box bogged down by something else that is causing this one not to be able to operate properly. Have you tried profiler to monitor box activity?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 1:32 pm
quote:
The sp is the code that checks for condition alerts to notify you about. You may have the box bogged down by something else that is causing this one not to be able to operate properly. Have you tried profiler to monitor box activity?"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Good idea. I will give that a try. Thank you
May 2, 2002 at 1:35 pm
John,
Also a good idea. I started down that path yesterday, just executing sections of the code. Unfortunately I got sidelined with other issues. I'll go back to that path and see what I get.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply