CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-agent-job
alexZ
alexZ

Reputation: 1

Execute long running jobs in SQL Server

I am working with SQL Server 2008. Using the Agent, I have created a job and scheduled it to execute every minute. The job executes a stored procedure that moves data from table XXX, to a temp table, and then eventually into table YYY.

The execution of the job may take more than one minute - since the data is rather large.

  1. Will a second instance of the job be started even though the first instance is still running?

  2. If so, should I mark records in temp table (status = 1) to indicate that those records are being processed by a previous instance of the job?

  3. Is there a way for me to check that an instance of the job is currently running, so that I don't initiate a second instance of the job?

  4. Is there another solution for this that I am unaware of? (throughput is important)

Upvotes: 0

Views: 1057

Answers (2)

Valentin Petkov
Valentin Petkov

Reputation: 1648

check this post
How to Prevent Sql Server Jobs to Run simultaneously

How to Prevent Sql Server Jobs to Run simultaneously

As Well HERE
Running Jobs
http://technet.microsoft.com/en-us/library/aa213815(v=sql.80).aspx

If a job has started according to its schedule, you cannot start another instance of that job on the same server until the scheduled job has completed. In multiserver environments, every target server can run one instance of the same job simultaneously.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452978

Only one instance of a particular job can run at any one time.

So there is no need to take any particular precautions against another execution of the same job beginning before the first one has stopped.

Upvotes: 3

Related Questions