SQL Job remains active when connection to DB encounters an issue

Document ID : KB000087947
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Error Message :
N/A

SQL Jobs remain active even when an issue with the database is encountered such as the TNS listener not found or a DB userid account is locked.  The SQL job does not fail and the only way to determine there is a problem is to look in the Job report where you will see the database error and then cancel the Job.  

If the SQL itself fails, then the Job will fail as expected.

 
Environment:
OS Version: N/A
Cause:
Cause type:
Configuration
Root Cause: Database connection errors do not cause SQL jobs to fail.
Resolution:
Use the "Runtime" tab of the SQL Job to set a maximum runtime (MRT).  If the MRT is reached, execute a tasks to check for connectivity to the DB and send an email/msg/Notification. Then cancel the SQL Job.

Fix Status: No Fix

Fix Version(s):
N/A
Additional Information:
Workaround :
N/A