Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

There is a table | jobID | status | startTime | endTime | and a job, that is scheduled to run every X seconds, it will take the first undone job ID, put it in stored proc and run it. Stored proc takes 1-2 minutes to run.

The users can:

  1. Add jobs to the table described above
  2. Take them out of the schedule

I can think of at least 2 risks that need to be avoided:

  1. Start a job just after someone has requested to take the job with this very ID out of the schedule
  2. Stop the wrong job: job stopping request was placed when the 'requested-to-stop' job was about to be completed, and is being executed while the next job, the one that we want to complete, is already running.

Performace is not too much of an issue, but, for some reason, locking the whole| jobID | status | startTime | endTime | table takes more than 30sec, and it times out.

Updating status column is the obvious solution, but, if the timings of status changing and job staring coincide, there will be a problem (I guess my problem is simuilar to multithreading problem).

What is the best way to solve it? I think is this must be a very common problem, but I cannot think of the right key words to google it. Many thanks in advace!

share|improve this question
Are you using SQL Server Agent for running these jobs? And is this system creating stored procs on the fly? – Max Vernon Mar 11 at 16:42
Yes, SQL Server Agent. – Yulia V Mar 11 at 16:45
Well timing is important. If the job starts right before someone took it out of the schedule, is SQL Server Agent supposed to go back in time? You could give them the power to also stop the job manually I suppose, but you can't bend time. – Aaron Bertrand Mar 11 at 16:46
Indeed I stop the job manually, using msdb.dbo.sp_stop_job – Yulia V Mar 11 at 16:53
So it's not clear what solution you're looking for. You can obviously code your UI to use whatever logic you want to determine whether or not removing a job from a schedule also means stopping any active instances of that job. Also if you are timing out trying to lock the whole table, perhaps it isn't indexed correctly to support your application. Do you really need to lock the whole table when modifying a single job? Are you keeping all the history for all jobs in that table, and never archiving any data? – Aaron Bertrand Mar 11 at 16:57
show 2 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.