question

aRookieBIdev avatar image
aRookieBIdev asked

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
sql-server-2008sql-agentjobs
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
You can start jobs by issuing the [`sp_start_job`][1] stored procedure [1]: http://msdn.microsoft.com/en-us/library/ms186757.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Actually I meant to start all the jobs in the sql agent at one instant after i restart my sql server agent.
0 Likes 0 ·
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.
0 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.