There are situations when a running job fails and remains locked in running state. It usually happens when the JMS is stopped or other fatal errors occur.
To unlock such a job, the following steps should be performed (in this order until it's unlocked).
1) Check if the job is locked, using the following DQL:
select r_lock_date from dm_job where job_name='[JOB_NAME]'
if the returned value is not nulldate, the job is locked. Unlock it by executing the API command:
unlock,c,[r_object_id]
2) If the job is still in running state, perhaps the job's process is still active (hung) and must be killed.
a. Get the process ID, using the DQL:
select a_last_process_id from dm_job where object_name='[JOB_NAME]'
b. Using Process Explorer (or similar) locate the process by id and kill it
c. Check the job state (in DA)
3) Checked the job and it's still running? Don't give up, we're almost done!
Check the a_current_status attribute value (DQL: select a_current_status from dm_job where object_name='[JOB_NAME]').
If it's 'STARTED', we should change it to 'FAILED'. Use the following DQL:
update dm_job object set a_current_status='FAILED' where object_name='[JOB_NAME]'
4) If your job still didn't get rid of the running status, perhaps it has the reference to the application that locked it.
Execute the DQL query:
select r_object_id, a_last_invocation, a_last_completion, a_special_app from dm_job where (((a_last_invocation IS NOT NULLDATE)
and (a_last_completion IS NULLDATE)) or (a_special_app = 'agentexec')) and (i_is_reference = 0 OR i_is_reference is NULL)
and (i_is_replica = 0 OR i_is_replica is NULL)
If the query returns your job, execute the following DQL:
update dm_job objects set a_special_app='' where object_name='[JOB_NAME]'
That's it, your job is unlocked now and ready to be run again.
To unlock such a job, the following steps should be performed (in this order until it's unlocked).
1) Check if the job is locked, using the following DQL:
select r_lock_date from dm_job where job_name='[JOB_NAME]'
if the returned value is not nulldate, the job is locked. Unlock it by executing the API command:
unlock,c,[r_object_id]
2) If the job is still in running state, perhaps the job's process is still active (hung) and must be killed.
a. Get the process ID, using the DQL:
select a_last_process_id from dm_job where object_name='[JOB_NAME]'
b. Using Process Explorer (or similar) locate the process by id and kill it
c. Check the job state (in DA)
3) Checked the job and it's still running? Don't give up, we're almost done!
Check the a_current_status attribute value (DQL: select a_current_status from dm_job where object_name='[JOB_NAME]').
If it's 'STARTED', we should change it to 'FAILED'. Use the following DQL:
update dm_job object set a_current_status='FAILED' where object_name='[JOB_NAME]'
4) If your job still didn't get rid of the running status, perhaps it has the reference to the application that locked it.
Execute the DQL query:
select r_object_id, a_last_invocation, a_last_completion, a_special_app from dm_job where (((a_last_invocation IS NOT NULLDATE)
and (a_last_completion IS NULLDATE)) or (a_special_app = 'agentexec')) and (i_is_reference = 0 OR i_is_reference is NULL)
and (i_is_replica = 0 OR i_is_replica is NULL)
If the query returns your job, execute the following DQL:
update dm_job objects set a_special_app='' where object_name='[JOB_NAME]'
That's it, your job is unlocked now and ready to be run again.
Thanks, great guide. It worked for me!
ReplyDeletethanks saved me
ReplyDeleteThank you very much.
ReplyDeleteThanks. helped me unlock a job
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThank you very much Great Guide !!!
ReplyDeleteThank you so much for this. The very last command stopped the job for me:
ReplyDeleteupdate dm_job objects set a_special_app='' where object_name='[JOB_NAME]'