0

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)

Simcha Khabinsky
  • 1,970
  • 2
  • 19
  • 34
alexZ
  • 1
  • 1
  • 1
    Yeah, you can have only one instance, MS suggest working with MSX/TSX to provide this functionality - http://connect.microsoft.com/SQLServer/feedback/details/633140/sql-server-agent-allow-multiple-instances-of-single-job You can think about having copies of that job where every job is having it' ID, and then put this ID into your status column to mark data to proceed – Silx Mar 30 '11 at 09:43

2 Answers2

3

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    Reason for downvote? This is absolutely correct. The same job cannot be executing concurrently with itself. So answer to question ".In this situation new execute job started?" is "No". Making question 2 moot. – Martin Smith Jan 13 '14 at 15:59
  • +1 I had thought that it was one instance per SCHEDULE, not JOB itself. But, I just tested and it is one instance per Job, even if there are overlapping schedules. Hence, I also agree that there is no point in answering questions 2 - 4 as they were all contingent upon #1 being "Yes", and it was not. – Solomon Rutzky Mar 07 '14 at 20:28
0

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.

Community
  • 1
  • 1
Valentin Petkov
  • 1,570
  • 18
  • 23