Start SQLServerAgent job Synchronously

From: Nathan Holmes (Holmes_at_discussions.microsoft.com)
Date: 08/08/04 

Date: Sun, 8 Aug 2004 03:07:03 -0700

Is there a SQL command that allows you to start a SQLServerAgent job such
that control doesn't return to the caller until after the job has completed?
sp_start_job lets me start a job (self-evidently), but it's an asynchronous
call and the caller resumes processing as soon as the call is made. I want
the caller to wait until after the called job has finished.

Background:
I have a set of SQLServerAgent jobs, each of which runs a BACKUP DATABASE
statement for a single database. I want to run several of these backups at
around the same time every day. I am assuming it is better to reduce disk
contention by ensuring that only one job runs at once than to let them all
fight it out for I/O at the same time. I thought I could do this by creating
a further 'control' job in SQLServerAgent with a number of steps to call each
backup job in turn, waiting for completion before calling the next job, but
apparently not - unless you can help me.

Yes, I could combine the existing jobs into a single job or introduce 'best
guess' lags between the job start times, but (a) I like the one job per
database approach because it gives me a one-click way of taking an adhoc
backup and (b) I'd have to keep altering my lag times as the databases grow.
The 'start job, wait, start next job' solution seems so neat that I'd be
surprised if it can't be done. Any help would be greatly appreciated.

Relevant Pages

  • Start SQLServerAgent job Synchronously
    ... Is there a SQL command that allows you to start a SQLServerAgent job such ... call and the caller resumes processing as soon as the call is made. ... I have a set of SQLServerAgent jobs, each of which runs a BACKUP DATABASE ...
    (microsoft.public.sqlserver.server)
  • Re: Start SQLServerAgent job Synchronously
    ... Wayne Snyder, MCDBA, SQL Server MVP ... > call and the caller resumes processing as soon as the call is made. ... > I have a set of SQLServerAgent jobs, each of which runs a BACKUP DATABASE ...
    (microsoft.public.sqlserver.server)
  • Re: Start SQLServerAgent job Synchronously
    ... Wayne Snyder, MCDBA, SQL Server MVP ... > call and the caller resumes processing as soon as the call is made. ... > I have a set of SQLServerAgent jobs, each of which runs a BACKUP DATABASE ...
    (microsoft.public.sqlserver.programming)
  • Re: New Small Business Taxi Cab Database
    ... I'm trying to build a database to keep track of information that will ... be stored when my client kicks off his brand new Taxi Cab business. ... Middle Initial of Caller ... Do a google search and read up on normalization ...
    (comp.databases)
  • Re: Start SQLServerAgent job Synchronously
    ... >> call and the caller resumes processing as soon as the call is made. ... >> I have a set of SQLServerAgent jobs, each of which runs a BACKUP DATABASE ... >> statement for a single database. ...
    (microsoft.public.sqlserver.server)
Loading