A really very interesting situation I faced with my client while working on Comprehensive Database Performance Health Check. They wanted to run SQL Server Agent Job After Completing Another Job. Well, it is possible and very easy to do.
There is no need to guess the time of the first job to complete or also there is no need to write complicated jobs to look for job status or job completion history. It is very easy to start another job when one job completes.
When you are creating various steps for the Job, create the last step with the help of the system stored procedure sp_start_job.
Start Agent Job
USE msdb ; GO EXEC dbo.sp_start_job N'Name of Job' ; GO
When the last step runs with the above SP, it will automatically run the next job. The solution is very simple but I have not seen many using this in the industry.
Here are my few recent videos and I would like to know what is your feedback about them. Do not forget to subscribe SQL in Sixty Seconds series.
- MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
- Tuning Query Cost 100% – SQL in Sixty Seconds #181
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
Reference: Pinal Dave (http://blog.SQLAuthority.com)
I have used this method many times. I am surprised to hear you state “I have not seen many using this in the industry.”.
What if the first job is executed on a different server and the execution of 2nd job causes deadlocks if the first job is still running? Because the source for the 2nd job is the destination for first … How will you implement a solution for that?