I have a Batch Job which got stuck at Executing status and I read that by setting it to Cancelling and then restarting the batch server AOS, this will resolve the problem. However, after doing so the status is stuck at Cancelling and not changing. I'm also not able to change the status manually due to it being in a Cancelling status. Any idea how this is fixed?
Check to see what batch group is associated with the jobs, and what AOS is actually responsible for executing that group of batches. Are you sure there is a AOS picking them up?
I created a Batch Group called "Invoice". It has a Batch Server attached to it (My AOS) and it is configured to be a batch server (Server Configuration). What do you mean by AOS picking them up?
Batch jobs are associated with a particular AOS at any time, and if this AOS is configured to be batch server, it will "pick them up" and run them according to the recurrence settings.
If you can't stop the batches, and they remain stuck in Cancelling, you have two options; 1) kill the associated SPID on the SQL Server (in case it is stuck there), or 2) simply restart the AOS.
What you could try, since this is your sandbox/playground AOS (afaik), is to stop the AOS, head over to the DB and update the table Batch by running this query; UPDATE BATCH SET STATUS = 0 WHERE STATUS > 6, then BatchJob with UPDATE BATCHJOB SET STATUS = 0 WHERE STATUS > 6. Then start the AOS and continue from there. There could be something wrong with the jobs themself. You should also just open the table browser (or SQL Query the tables) and see if the AOSID is correct for the jobs in question.
I have already tried restarting the AOS but this didn't work.
How do I find out which SPID the batch is using? I've looked on SQL server and there were 0 results returned when I did a SELECT * FROM BATCH WHERE STATUS > 6. The task's in my Batch Job have a status of Ended
So this means it is no longer stuck in Cancelling. Have you tried to start them again?
A batchjob with status Ended or Hold will surely not have any Process ID (SPID), since it is idle.
When I did SELECT * FROM BATCHJOB WHERE STATUS > 6 I can see my Batch Job and it has a status of 7. It seems BATCH show's the Tasks associated to a Batch Job?
Well, it can be various reasons for it to be stuck in Cancelling, but I see no harm in setting it to STATUS = 0, so you can try start it up again. :-)
But it first got stuck in Executing, hence why I had to put it into Cancelling, so I think there is a wider issue here..
Hi Tommy, using exec sp_who, how can I find the spid of a batch process that is sleeping or tying up batch jobs? I have a batch job that has had no progress (relating to data upgrade cockpit), and I have verified all the tables and ensured no other batch jobs are running. I have automatic role assignment and user license counts on withhold and a status of '0'. I have set up three batch servers all with the dataupgrade batch job...still no progress, no logs or errors, nothing...any ideas?
FYI, the batch job has been sitting in waiting, not executing. I verified the batch groups, batch servers, etc are all configured correctly.
I decided to run a CIL compile, and that cleared my issue.. thanks