December 28, 2007 at 12:26 pm
I have several SQL Agent jobs that will occasionally just "hang" after a particular step. They don't raise an error, nor do they complete, they just sit, preventing the same job from running on a normal scheduled basis.
Is there some way that I can proactively find this situation so I don't have to rely on my users calling me to say that they are missing data so something is wrong? :unsure:
December 31, 2007 at 9:57 am
you may be able to view waits, sometimes these shows as sos_schedulers, if I remember correctly.
What I will say is that this state of affairs should not happen so there must be some other root cause you should search out. w2k3 requires sp2 to avoid a few o/s based problems and as always you should make sure you have the latest sql service pack and rollup, I'd recommend 3186 at least.
I'm pretty sure waits will do this for you, failing that you need to write a query to alert you when a job is running too long or has not run on its nect scheduled time - either are fairly simple to write against msdb.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 31, 2007 at 10:44 am
Can you add some logging to the steps, to be sure it's completed? Maybe an insert into a table as the last part of a proc or package?
December 31, 2007 at 10:44 am
Also be sure if you have a package or something that you're not waiting for user input. If you are, you're stuck.
January 1, 2008 at 12:50 pm
ah yes - now open a can of worms! You may have a step waiting on a console type session/response, you could be waiting on a connection response, e.g. ftp etc.
I'd probably suggest extratcing the steps to a query window and running through them to see exactly what they do.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 2, 2008 at 6:22 am
My problem is not one where a particular job step fails or hangs, a step successfully completes and then the job itself just hangs. Usually the job executes just find but every once in a while this happens.
I've checked for conflicts with anything else that may be running and there's no interference. I've tried running the steps individually and they work fine.
I've tried logging the job steps to a table, where each step writes a record that it has started and then deletes that record when it completes; well, it works, it's just that the step completes.
I tell you, in over 30 years of doing this, I've never seen anything like this happen; but then again, I've never seen anything as flexible and useful as SSIS.:ermm:
January 3, 2008 at 9:55 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply