x

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

aRookieBIdev gravatar image

aRookieBIdev
2.3k 51 56 61

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

2 answers: sort newest

You can start jobs by issuing the [sp_start_job][1] stored procedure

[1]: http://msdn.microsoft.com/en-us/library/ms186757.aspx
more ▼

answered Oct 12, 2011 at 07:19 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

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

Shawn_Melton gravatar image

Shawn_Melton
5.3k 20 21 29

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1841
x90
x21

asked: Oct 12, 2011 at 07:17 AM

Seen: 1473 times

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