Even though RES Automation Manager is built rock solid from the ground up, there may be events that may cause a job to hang for one reason or another. This may for example happen when you are running an Interim version of Automation Manager. The result is that all of a sudden a job just sits there in the current activity. Even worse, when you then hit the abort button, the job will keep hanging there with the Aborted status until the four horsemen arrive. So, what can you do about it? Here is a solution for it. It has been tested on Automation Manager 2011 IR2
Now, this is where I have to pull the big handbrake before we go any further and issue a general warning. What we are about to go into is NOT for novices, meaning if you have no clue to what a SQL update script is, you should probably call somebody more experienced. At the very least backup your Automation Manager SQL database before you proceed. Second, you should not do something like this before consulting with RES Support. This goes for all database hacking. Third, if you screw this up – nobody can be held responsible but yourself. There, it’s said, so let’s move on.
There are two SQL statements which can help you. The first one below will not change anything, but will simply query any Aborting jobs (lngStatus = 2), thus displaying the offending record, which is gumming up the job:
select * from tblMasterJob where lngStatus = 2 order by dtmStartDateTime
The next query is the actual update query, which will forcefully arbort all jobs which are stuck in the Aborting state. That means that if you have any repeat scheduled jobs, they are also likely to be nuked out of Current activity, so you’ll have to reschedule them later:
update tblMasterJob set lngStatus = 3 where lngStatus = 2
When you execute the update query it should return something like ‘1 row(s) affected by query’, which tells you how many records were modified. Usually this would only be one.
If you would like to further query the status of jobs in the Automation Manager environment, you can use the first query above. Below is a guide to the values of lngStatus in tblMasterJob:
-1 = Disabled 0 = Scheduled 1 = Active 2 = Aborting 3 = Aborted 4 = Completed 5 = Failed 6 = Failed Halted 7 = Cancelled 8 = Completed with Errors 9 = Skipped
Note that values 6 and 9 probably never appears in tblMasterJob as these status values may be used in other tables such as tblJob and tblJobStats. Again, let’s not go bananas here gents. Use the information with care.