There are three situations where jobs can be cancelled. A user can cancel a job while it is being executed, prevent it from running at all, or a job may stop while executing due to some operating system intervention.
Many SQL agents trigger jobs that run specific maintenance functions – one example is transaction log backup. In cases where the job was running infrastructure maintenance operations such as backup and indexing, its interruption after launch has a negative impact on performance, which will be restored only when the same job is run to completion.
In cases of application-related jobs, our recommended best practice is to set up proper notification procedures in each agent so that appropriate notification will be given in case of failure, enabling follow-up action to be taken.
Find out how you can save hours of work when investigating the root cause of this issue.
Symptoms
An SQL job has been cancelled.
Impact: High
This alert notifies that the designated SQL job has not finished. This may cause loss of data and disruption of the application’s integrity or negatively impact the system’s performance.
Expected behavior
SQL jobs should not be stopped while running and should run without reducing performance or harming the application’s activity.
Possible causes
1- User action. Priority: Medium
Users might manually stop a job that’s running longer than expected or prevent it from running if there are concerns about its impact on the system.
Problem identification:
When identifying that the job was canceled, look for messages mentioning that it’s a user’s intervention and not anything else.
- Connect to the SQL Server Management Studio, navigate to SQL Server Agent, go to Job History Logs, and follow the steps. Look for specific job history entries related to job steps being stopped manually. This will be mentioned in a “message”.
- Check audit logs to track user activity records of interactions related to job cancellation.
- Review system logs or security logs on the host server for any related events that might indicate user interventions for job cancellation.
Recommended action :
Confirm the need for the intervention by the user who stopped the job. Track the job event to locate the step at which it was stopped and rerun if needed.
2- Operating System action. Priority: Medium
In cases when the job consumes excessive OS resources (CPU, Memory, Disk I/O) and degrades performance, it might be cancelled by the OS. A sudden system restart or shutdown might cause this cancellation as well.
Problem identification:
Identify if a job was canceled and that the Operating System itself initiated it. Look for a shutdown event or interference of the job with other OS tasks scheduled.
- Connect to the SQL Server Management Studio, navigate to SQL Server Agent, go to Job History Logs, and follow the steps. Look for specific job history entries related to job steps being cancelled and why. This will be mentioned in a “message.”
- Track the next execution of the job by putting a trace before its next execution. Filter by exceptions in order to identify the correct error message and leading events.
- Investigate the exceptions and check for solutions. Look for more details: query code, the reason for the error, the server’s status at the moment, and more.
Recommended action :
Make sure that urgent system events and tasks are scheduled at times different from the scheduled jobs, in order to prevent a conflict between them. If possible, schedule jobs to execute at low system activity periods.
3- The job’s execution is incorrect or facing errors in the job’s step. Priority: Medium
A job may be cancelled after it starts its execution due to some error in one of its steps or tasks. There might be several reasons for it: syntax errors, misconfigured parameters, issues with external resources, or conflict with other jobs.
Problem identification:
- Using tracking tools such as SQL Server Profiler (for SQL Server) or Oracle Enterprise Manager might help to capture events related to increased data activity which is measured by data page allocations. Take into account that you have to wait until getting efficient results since history data is not provided.
- Other options might be using database logs or DMVs (dynamic management views), which should be done by someone familiar with these tools.
- Investigate the queries’ code in order to prevent them from causing data growth, if possible.
Recommended action :
Following the exception’s error message, you are able to know how to handle the specific error. Track the next job’s executions, or else test the steps that caused the issue if possible.
