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
You can start jobs by issuing the [`sp_start_job`][1] stored procedure [1]:
http://msdn.microsoft.com/en-us/library/ms186757.aspx
Actually I meant to start all the jobs in the sql agent at one instant after i restart my sql server agent.
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.
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()}
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.
