Sql server Jobs Kick Start

Hi ,

I have around 20 jobs in my agent. Each time when i restart my job i need to restart all the jobs.Is there any easy way around using a sql query instead of doing this one by one.

Thanks in Advance. Kannan

more ▼

asked Oct 12, 2011 at 07:17 AM in Default

avatar image

2.8k 56 65 71

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You can start jobs by issuing the `sp_start_job` stored procedure

more ▼

answered Oct 12, 2011 at 07:19 AM

avatar image

Kev Riley ♦♦
66.1k 48 63 81

Actually I meant to start all the jobs in the sql agent at one instant after i restart my sql server agent.

Oct 12, 2011 at 07:50 AM aRookieBIdev

Maybe there is an alert condition you can define, which corresponds to the Agent starting, which can then fire a job which in turn fires all your other jobs using the sp above.

How you do this, I'm not too certain - the agent is what fires the alerts, so maybe capturing the agent starting is not viable. The Agent starting is written to the windows log, but whether or not the latency between writing the event and the agent reading the log is enough to capture it - I don't know.

Another alternative may be Extended Events.

Oct 12, 2011 at 08:03 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

PowerShell allows you to interact fairly easily with SQL Agent jobs. If you wanted to have one job that could fire off the other 20 you can create a SQL Agent job and set it to fire off a PowerShell script similar to below. This would just fire off every job that started with "Test".

DIR SQLSERVER:\SQL\HAL9000\SQL2K8\JobServer\Jobs\Test* | % {$_.Start()}
If you have other jobs that you don't want to start just filter out the results of the dir command. You could also just put all the job names into a variable and iterate through a foreach loop using the Start().

Once you get the script doing what you need just create a SQL Agent job to fire it off. If you want it to happen anytime the SQL Agent service is restarted, configure the service to run PowerShell.exe and have it call the script you create. You can do this under the Services applet and go to the "Recovery" tab of the SQL Server Agent properties window.

I would make sure you document how you set this up so anyone else on your team or that you work with is aware of how it is configured.

more ▼

answered Oct 12, 2011 at 11:21 AM

avatar image

6.5k 21 25 34

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 12, 2011 at 07:17 AM

Seen: 4450 times

Last Updated: Oct 12, 2011 at 07:17 AM

Copyright 2017 Redgate Software. Privacy Policy