We sometimes get jobs completed abnormally when a SQL operation is the victim of a deadlock. We would like to parse the output to search for the "deadlock victim" exact string in the error message, and rerun the failed job if so. Is this possible? I have an event setup and can capture the job output, but is there a way to search through it and rerun the specific job that failed? Thanks.
I may not understand this correctly since I am not a job creation expert, but you should be able to parse out in the output for a string pattern to control job completion status in the Run tab of the job definition - for example click on abnormal completion when string "Deadlock detected" is found . Then based on the job status create a job event that will run another occurence of the same job. I would be curious though if there is another way I was not thinking about.
That's exactly what we came up with here after lots of discussions! But the problem with this method is that we can submit a new instance of the job but can't rerun the *same* instance. This will cause versioning issues and require manual intervention in the end, which is what we're trying to avoid.
How about the 'rerun each time dependencies are met' check box in the dependency tab? Maybe you can use a variable dependency. Like a job event after the job runs thats sets a variable = 1 if job failed and 0 if success. Then use that variable as a dependency.
Or maybe you can even use the same job as a job dependency - like if that job completes abnormally. Again never tried it - it may give you a warning or not allow you to alltogether.
How would I be able to run ANY job that might have "Deadlock" in the <Job Output>
If it was just for 1 job I would store the output of each job into a variable and then create an event/action sequence that reruns the job based on the output but is there an easy way to do this for all Tidal jobs?
You can also create a new Job Event and select as an action Rerun, which is listed in the available Actions at the end of the list, and use the event trigger "job Completed Abnormally" This available action it is already included when you install Tidal "Out of the box". However, by using this action, the jobs will rerun automatically right away until there is a "Job Completed normally" condition or you select a Maximum Reruns count when you build this event. This is available of TES 5.3.1 and TES 6X.