June 12, 2009 at 9:11 am
Hello,
We have several DTSs updating different DBs on the same server. The DTSs are run through VB6 exe's. I have one job that should take around 1.5 hours, but depending on the day and other running DTSs it can take all day. The previously mentioned DTS consists of probably 30-40 individual sql statements in one script. I added some logging sql before and after each sql statement so I can track the progress of the whole script. What I see is that a statement that usually takes several minutes under normal conditions is taking hours. One time the same statement was going on 12 hours before I killed it. Then the next day the whole entire DTS ran in 1.5 hours like it is supposed to. It seems the server is being overloaded and I'd like to move the db that the DTS updates to another computer, but can't right now. So that brings me to my question. Can I pause a process? Using sp_who2 I can see what is running and what I'd like to do is pause other processes which take hours so the DTS in question can finish and then the longer running DTSs can resume.
It's almost like the DTS in question is forgotten about and just remains sleeping. I don't think that is the case because I can see that the cpu time increases a little bit over the course of the day. I guess another option might be to change the priority of a process if that is possible. We are using sql 2k on win2k.
Thanks,
Gunner
June 12, 2009 at 9:28 am
I don't think you can change the priority of a single DTS process. What you can do is build checks into those processes, perhaps at places, and query to see if the other process is running. If so, run a WAITFOR, which will stop processing for xx minutes or until xx time.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply