✔ SOLVED: Automating retry of SQL failed jobs based on error message keywords.
SOLUTION # 1
To configure a retry of a particular job in SQL Server based on certain keywords in the failed job message, you can use a SQL Server Agent Alert and a SQL Server Agent Job to automatically retry the job when the alert is triggered. Here’s an example of how to set this up:
- In SQL Server Management Studio, expand the “SQL Server Agent” node and then right-click on the “Alerts” folder. Choose “New Alert…” to create a new alert.
- On the “General” page, give your alert a name and description and then choose “SQL Server performance condition alert” as the alert type.
- On the “Response” page, choose “Execute a job” as the response type and select the job that you want to retry as the “Job to execute”.
- On the “Conditions” page, specify the conditions that should trigger the alert. For example, you can use the “Message text” condition to specify keywords that should trigger the alert.
- Click “OK” to save the alert.
- Now, whenever the specified job fails and the error message contains the specified keywords, the alert will be triggered and the job will be automatically retried.
OTHER POSSIBLE SOLUTIONS
- Use a custom script to check the error message and retry the job if the error message contains the specified keywords. You can use a SQL Server Agent Job to execute the script on a regular basis, or you can use a SQL Server Agent Alert to execute the script whenever the job fails.
- You can use SQL Server Integration Services (SSIS) to create a package that includes an “Execute SQL Task” to retry the job and a “Script Task” to check the error message and decide whether to retry the job or not. You can then execute the SSIS package using a SQL Server Agent Job.
- Use a custom stored procedure to check the error message and retry the job if the error message contains the specified keywords. You can then use a SQL Server Agent Job to execute the stored procedure on a regular basis, or you can use a SQL Server Agent Alert to execute the stored procedure whenever the job fails.
- Use a SQL Server Extended Event to capture the error message and send it to a custom script or application that can check the error message and decide whether to retry the job or not.
3RD PARTY SOLUTIONS
There are several third-party tools that can help you automate the process of retrying a failed SQL Server job based on certain keywords in the error message. Some options include:
- Redgate SQL Monitor: This tool provides real-time monitoring and alerts for SQL Server, including the ability to automatically retry failed jobs based on specified criteria.
- Idera SQL Doctor: This tool provides performance tuning, diagnostics, and monitoring for SQL Server, including the ability to automatically retry failed jobs based on specified conditions.
- ApexSQL Job Manager: This tool provides job scheduling and management for SQL Server, including the ability to automatically retry failed jobs based on specified conditions.
- IDERA SQL Safe Backup: This tool provides backup and recovery for SQL Server, including the ability to automatically retry failed backups based on specified conditions.
Some of the above tools may offer a free trial or limited free version, but in general, they are not free to use. Most of these tools are commercial products that require a license to use.