July 28, 2003 at 4:05 am
Hi, I wonder if anyone can help me?
We are running SQL Server 2000 SP3 and have been relatively problem free.
However, recently we had a new database written by an external company. This was initially put onto our development box and is now on our live box.
This database has several Stored Procedures that run as SQL Agent Tasks, none of which appear to be particularly complicated. But one of them is used to send email messages. This uses CDONTS, and not SQL Mail.
We have noticed on both servers that everything seems to run well, but after about 6 weeks, the system just totally slows down and almost grinds to a halt. This affects all databases. Rebooting the server fixes the problem.
I am fairly certain that this must be caused by the new database, or running CDONTS, but need to get some solid proof in order to confront the external company. The trouble is that, when the problem occurs, there's been no obvious problems being reported. Can anyone point me in the right direction, or has anyone else had this problem?
Thank you in advance for any help.
July 28, 2003 at 6:35 am
I suggest starting with a regularly scheduled profiler trace, capturing Batch and RPC Completion events and certainly the database amongst the columns.
You could have it scheduled for several times a day, running for say 5 mins and a maximum of 1mb output file. The output file name should be constructed from the current date/time.
If the output is too "noisy" you could filter out very low cpu and/or duration events.
Having this information available allows you to load it to SQL tables or query the trace files directly from QA, and thus identify trends and possibly the culprit.
Speaking of trends... does job history show the execution times of the new database's scheduled jobs starting to blow out at some stage?
Cheers,
- Mark
July 28, 2003 at 8:36 am
Thank you for your suggestion - I'll give it a go.
As far as looking at the scheduled jobs is concerned, I have already checked the job histories of all jobs, not just those of the new database, and there does not appear to be any slowdown at all. It's just as if SQL Server is running along quite happily and then, all of a sudden, has a nervous breakdown!
August 1, 2003 at 12:25 pm
If they are using cdonts for mailing you should check to make sure they are cleaning up the objects correctly in the stored proc. You can also change the procs to run "in process" or "out of process" meaning whether it spawns within the sql server process instance or out by itself.
It's been awhile since I've done it. I can look some of it up if you need help with it.
Tom
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply