Sql-server – Resume job ‘A’ at some step till job ‘B’ is completed

SQL Sentry has a facility built exactly for this: to chain jobs and arrange them in various workflow orders.

I started using SQL Sentry years ago, before I ever joined the company, to do exactly this. What I wanted was a way to start a restore job on our test server immediately after the backup on production had finished.

What I had originally implemented was just a substantial buffer between the backup job start time and the restore start time. This wasn't exactly foolproof; since backup times varied the buffer often left us with wasted time where a restore hadn't started even though it could have. And occasionally the buffer wasn't enough.

What I implemented next was similar to what you have - I wrote a job on the test server that started shortly after the scheduled backup, and kept polling to see when the job was finished. That was later amended to just have a second step in the backup job that updated a table on the test server. Not really much different, except the restore job only had to watch a table locally instead of monitoring the job history remotely. Thinking back this could have been a trigger on that table that called sp_start_job so the job didn't have to run every n minutes (or be scheduled at all).

The final solution was to chain jobs together ... when the backup on server A finishes, Event Manager starts the restore job on server B. And if there was a third job, and a fourth job, or conditional logic based on what to do when a job fails vs. succeeds, etc., this can all be accounted for. The workflow designer will remind you quite a bit of SSIS:

The underlying mechanics of what I'm describing is not rocket surgery, of course. You could write this type of chaining wrapper yourself if you sat down and did it. Just providing you one alternative where you don't have to.

You could create a Stored Proc for steps 1-4, with error handling that notifies you via email on failure.

Something like:

CREATE PROCEDURE TestEmailOnFail
AS
BEGIN
    BEGIN TRY
        /*
            Perform some action that might fail
        */
        SELECT 0/0; --THIS WILL FAIL
    END TRY
    BEGIN CATCH
        DECLARE @subject nvarchar(max) = 'Job Failure Notification';
        DECLARE @body nvarchar(max) = 'TestEmailOnFail Job Failed' 
            + CHAR(10) + CHAR(13) + 'Error Number:  ' + CAST(ERROR_NUMBER() AS nvarchar(max))
            + CHAR(10) + CHAR(13) + 'Error Message: ' + ERROR_MESSAGE();
        DECLARE @to nvarchar(max) = 'somebody@email.com';
        DECLARE @profile_name sysname = 'SQLMailProfileName';
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
            @recipients = @to, @subject = @subject, @body = @body;
    END CATCH
END

I just tested this using my SQL Mail Profile, and my email address and got the following email:

TestEmailOnFail Job Failed
Error Number:  8134
Error Message: Divide by zero error encountered.