February 26, 2009 at 7:01 am
I am running a sql agent job which i run every week and it takes around 23hrs to finish. but today i i observed is the job is still running, i see the status as " Executing" i it dodnt even finish 1st step out of 5 steps and it is running for over 37hrs. The jov dont even have a job history. What is happening with my job , i have no idea.
February 26, 2009 at 7:10 am
execute sp_who2
and check whether your job is blocked by some other process
to see what command is executing execute dbcc inputbuffer([yourProcessID])
(you can get the process id from sp_who2)
BTW what kind of processing are you doing that takes 23 hours?
-Vikas Bindra
February 26, 2009 at 7:28 pm
since my job was running for very long and i didnt see any status when i checked " view history" i forced job to stop and i ran the t-sql manually from query window. Though my job was stopped but still it appeared in the activity when i checked through " sp_who2" may be it was rolling back all transactions. again it took 8 hrs to roll back and this roll back was blking my query which i was running manually. once the roll back was done then again my actuall started running gud.
I am just posting my expereince with this just to make sure if my understanding was right and would like to know if there is way we can get rid of such issues. This process wasted my whole day.
sometimes i am confused with status it shows in the activity monitor. why does it show suspended for an active query?
February 27, 2009 at 12:56 am
Mike Levan (2/26/2009)
sometimes i am confused with status it shows in the activity monitor. why does it show suspended for an active query?
The process goes in suspended mode if some I/O is happening.
-Vikas Bindra
February 27, 2009 at 5:24 am
I am not sure what is going on with my updates. ITs been 20 hrs still the status is in suspending mode, i think itsd not good but my updatres are kind of urgent. How do i troubleshoot this , if i restart the sql server service and re run the job will it be ok or again does it start rollback transactions for longer time. what wud be my best option.
February 27, 2009 at 6:10 am
Check the server's memory, disk, and processor status and if any of these is out of capacity, you may get the cause of issue. Some processes get suspended for long time if they do not get enough resources (specially memory) to start process. Try to release resources by closing other processes those are not important.
Restarting the server will also rollback the changes so that may also take time if there is huge changes. But sometime stopping a job does not start the rollback and we need to kill the process of that job explicitly by kill command.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply