Run a Job in Parallel?

Hi,
We have a job that I would like to run every 1 minute. For business &
performance reasons we would like the job to be able to run itself in
paralell - aka if there is alot of work to do the job can start a second, or
third, etc. instance of itself.
However we get this error whenever we ry to start the second instance:
Executed as user: CFC\00SQLSRVQA. SQLServerAgent Error: Request to run job
SLARunReport (from User CFC\00SQLSRVQA) refused because the job is already
running from a request by Schedule 2 (1 Minute Intervals). [SQLSTATE 42000]
(Error 22022). The step failed.
Is there any way around this? Is there a way to allow a job to run in
paralell?
--RichardRichard wrote:
> Hi,
> We have a job that I would like to run every 1 minute. For business &
> performance reasons we would like the job to be able to run itself in
> paralell - aka if there is alot of work to do the job can start a
> second, or third, etc. instance of itself.
> However we get this error whenever we ry to start the second instance:
> Executed as user: CFC\00SQLSRVQA. SQLServerAgent Error: Request to
> run job SLARunReport (from User CFC\00SQLSRVQA) refused because the
> job is already running from a request by Schedule 2 (1 Minute
> Intervals). [SQLSTATE 42000] (Error 22022). The step failed.
> Is there any way around this? Is there a way to allow a job to run in
> paralell?
> --Richard
You can create multiple jobs and execute as many of them as needed. You
can have Job1, for example, clone itself using T-SQL whenever it needs
to spawn another instance of itself.
One way to do this is to use SQL Enterprise Manager and have it generate
the T-SQL. Once you have it, it's just a matter of creating a unique job
name (the newid() function is great for this) in the T-SQL and
incorporating it into the existing job or make it a new job altogether.
If you have a job that creates the job you actually want to execute, you
can call the "creation" job as many times as you need since all it does
is quickly create and start the job.
David Gugick
Imceda Software
www.imceda.com|||Basically no there isn't. In a case like this I think it is best handled by
running sp's from an outside application that can be more flexible than SQL
Agent. But another option is to create two or three jobs that run at 1
minute intervals but are spaced 20 seconds apart. Just make sure what ever
code they are running is fully capable of doing the work without contention
with each other.
--
Andrew J. Kelly SQL MVP
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:2BB6E1EE-2D8D-46D1-8B20-8EBE3C199C34@.microsoft.com...
> Hi,
> We have a job that I would like to run every 1 minute. For business &
> performance reasons we would like the job to be able to run itself in
> paralell - aka if there is alot of work to do the job can start a second,
> or
> third, etc. instance of itself.
> However we get this error whenever we ry to start the second instance:
> Executed as user: CFC\00SQLSRVQA. SQLServerAgent Error: Request to run job
> SLARunReport (from User CFC\00SQLSRVQA) refused because the job is already
> running from a request by Schedule 2 (1 Minute Intervals). [SQLSTATE
> 42000]
> (Error 22022). The step failed.
> Is there any way around this? Is there a way to allow a job to run in
> paralell?
> --Richard
>
>