✔ 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:

1. 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.
2. On the “General” page, give your alert a name and description and then choose “SQL Server performance condition alert” as the alert type.
3. 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”.
4. 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.
5. Click “OK” to save the alert.
6. 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

1. 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.
2. 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.
3. 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.
4. 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:

1. 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.
2. 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.
3. 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.
4. 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.