January 17, 2013 at 5:43 pm
How can I schedule a sql job the following way:
1. If for the first time the SQL job fails, then after 15 minutes I want to retry the SQL job to run
2. If for the second time the SQL job fails, then after 15 minutes I want to retry the SQL job to run
3. If for the third time, the SQL job fails, I want to get email only for the third failure saying that the job has failed.
Thanks.
January 17, 2013 at 5:50 pm
It's possible to script a new job start time in the scheduler (SQL Agent) but that's probably a pain.
Something like this might do it for you:
DECLARE @TryCount INT = 0
WHILE @TryCount < 3
BEGIN
BEGIN TRY
EXEC YourProc
END TRY
BEGIN CATCH
IF @TryCount < 2 WAITFOR DELAY '00:15:00'
ELSE [Send your email]
END CATCH
SET @TryCount = @TryCount + 1
END
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 18, 2013 at 1:44 am
How many steps does the job have?
If 1, you could look at changing the advanced properties of the step to have a retry attempts value of 2 and a retry interval of 15.
Then setup SQL Agent Alert system using DBMail and operators and set the job to email you on failure.
January 18, 2013 at 4:41 am
anthony.green (1/18/2013)
How many steps does the job have?If 1, you could look at changing the advanced properties of the step to have a retry attempts value of 2 and a retry interval of 15.
Then setup SQL Agent Alert system using DBMail and operators and set the job to email you on failure.
I'm thinking I need to learn more about the agent. I didn't know you could set up retry attempts.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply