March 7, 2003 at 2:05 pm
Bob Musser has an excellent SQL Server Central article out this week on reducing system loading. He discovered unnecessary activity being generated by SQLAgent when one isn't using Alerts. He adds a very logical flow control statement into MSDB..sp_sqlagent_get_perf_counters. Bob was successful with SQL7. I can't begin to get anywhere with SQL 2000 SP2/SP3, regardless of which login I use or if I set the "Allow direct modification to system tables" configuration flag. Is it at all possible to modify system stored procedures in SQL 2000?
March 7, 2003 at 5:05 pm
Did you run the reconfigure with override?
Andy
March 8, 2003 at 7:51 am
I tried it via the Enterprise Manager / Server Properties and via SQL Query using Reconfigure with override. Still out in the cold. This has aroused my interest. Proceeding to dig the hole deeper! (to find the answer).
March 8, 2003 at 8:18 am
I'm able to make changes to my test machine that has allow updates set to 1.
Andy
March 10, 2003 at 5:19 am
I have made some progress! Triple checked Allow Updates complete with SQL Server restart and PC reboot. No difference in Enterprise Manager - could not mod sp. Then I tried Query Analyzer - drop proc, then re-Create Procedure. I'll be dipped if that didn't work! Why one and not the other? I will try it with the Allow Updates flag at 0 next to see if that is part of the equation. Has anyone every dealt with TSQL being 'versioned controlled'? I saw a lot of sp's that dealt with 'vcs' which I take to mean this. Ideas? I'm going to get back to my original intention which was to stop SQL Agent from consuming resources non-productively (Bob Musser's article) when one doesn't use alerts. Also seeing resource drain when SQLAgent is scheduled to run a job. Whether the job is enabled or not, it checks the registry to see how many rows are allowed in the job history table - total, and per job. The sp that does this calls repeatedly to the registry. Doesn't ring quite right and makes any Job a fairly expensive proposition.
More later - RC
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply