Sql-server – Getting SQL Server Agent to run multiple instance of a job at a time

jobssql-server-agent

As I understand it, SQL Server Agent runs only one instance of a job (SSIS Package in this case) at a time.

I need it to Start a job 15 times, and then let them all run at the same time.

Up to now, I have accomplished this quite successfully, manually, by right clicking on the job and selecting "Execute". But i need to automate this now.

I have tried to schedule the job to start 15 times, but it only
works sequentially. (1 job after the other)
I created 15 separate jobs and started them, but the first one starts
and the others not, stating the job is already in use.

Should I look at another scheduling tool to accomplish this?

Any pointers would be welcome…..

Best Answer

As stated in the Note of MSDN Create and Attach Schedules to Jobs

Only one instance of the job can be run at a time. If you try to run a job manually while it is running as scheduled, SQL Server Agent refuses the request.

Then I'd rather rewrite the SSIS package to do the paralelism inside the package. As I don't use SSIS - except with MAintenance Plans - I cannot tell you how to do that.

Related Solutions

Sql-server – How to launch SQL Server Agent job on completion of another one

Use sp_start_job.

Put that command into a job step as needed. No need to worry about the duration of previous jobs.

Sql-server – How SQL Server agent calculates next run date for a job

In the dbo.sysschedules table you will see all of the data needed to calculate the schedules.

The columns named active_* delimit the periods in which the jobs can run.

The columns named freq_* define the frequency of the job runs. You will notice a freq_type, which defines a type of frequency, which drives how the freq_interval and other columns are interpreted.

Please read the definitions at: http://msdn.microsoft.com/en-us/library/ms178644.aspx

EDIT: You can calculate the next_run_datetime by using one of the following:

sp_help_jobschedule @job_name = 'Jobname'
sp_help_jobschedule @job_id= '<job id guid>'

Since there can be several schedules you can also use the schedule_id as such:

sp_help_jobschedule @schedule_id = 1, @job_name = 'Jobname'

This will return a data set in which the last columns give the data, such as:

next_run_date   next_run_time   schedule_uid                             job_count
20141110        190000          301DC5D5-9569-49FA-8BC5-9129109AFA6A     1

And you can script out the msdb procedure sp_help_jobschedule to see the code in the procedure, in case you want to include some of that in your own script.

Best Answer

Related Solutions

Sql-server – How to launch SQL Server Agent job on completion of another one

Sql-server – How SQL Server agent calculates next run date for a job

Related Question