How to ensure a job only runs for a set duration?

Question

How to ensure a job only runs for a set duration?

winston Smith

SSCoach

Points: 19500
More actions
September 9, 2011 at 4:10 am

#244616

I have 60 mins a day for index maintenance. I want to rebuild the indexes one at a time, based on their usage, rebuilding the most used indexes first.
The job can only run for about 60 mins, give or take 10/15 mins. I know the duration will depend on index sizes etc, but in general, How do i put a time limit on a job, so that it will run for only a predetermined number of hours and minutes?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173056 More actions · Answer 1

So far as I know, you can't.

You could test rebuilding various indexes, though, and break the job up into smaller parts, only running one of those parts each day. That still won't guarantee that the job won't run long. There are a lot of factors that go into how quickly jobs run.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

You can start the job with sp_start_job and wait for its completion in a loop.

If the job exceeds the timeout you set, stop it with sp_stop_job.

-- Gianluca Sartori

ps. One Orange Chip Points: 29422 More actions · Answer 3

you can also have another job that keeps on checking the run status of this job and kills it if it exceeds the threshold. However, regular maintenance of databases are very important and you can split the job into multiple jobs that run for lesser duration. may be run them in parallel; if data files are in different drives, you should see faster execution.

Pradeep Singh

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

winston Smith (9/9/2011)
I have 60 mins a day for index maintenance. I want to rebuild the indexes one at a time, based on their usage, rebuilding the most used indexes first.
The job can only run for about 60 mins, give or take 10/15 mins. I know the duration will depend on index sizes etc, but in general, How do i put a time limit on a job, so that it will run for only a predetermined number of hours and minutes?

As you've identified, you'll need to build your own index maintenance stored procedure and loop through each index checking the current time of day just before running the index rebuild. Typically, I'll get all of the index stats and drop them in a temp table and then step through the temp table in the appropriate order.

If you lookup [font="Arial Black"]sys.dm_db_index_physical_stats[/font] in Books Online (the "help" system that comes with SQL Server), there's actually a fairly decent bit of code provided to step through indexes. You'll just need to tweek the code for current time of day and whether it should continue or not.

As a side bar, this is one of the very few places where I condone the use of a cursor or While Loop.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Hi

As ps. suggested, if your database consists of multiple data files, use parallel index maintenance jobs. Can you use a larger maintenance window on weekend?

Do you run a SQL Server Enterprise edition that supports online index rebuilds? If so, you could do the index maintenance online when system load is not to high.

Jeff Moden (9/11/2011)
As a side bar, this is one of the very few places where I condone the use of a cursor or While Loop.

Ha! Logged for future generations! Jeff condones a RBAR solution 😀

(Sure, this is makes much sense here but this is still unique to me :-))

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

This works only on the indexes that need work.

Fully customizable, including max time.

The max time is checked before doing the next task. So obviously this is not bullet proof, but pretty good.

http://sqlfool.com/2011/06/index-defrag-script-v4-1

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

Florian Reischl (9/11/2011)
Ha! Logged for future generations! Jeff condones a RBAR solution 😀

Heh... I could argue all day why it's not RBAR but I'll take the hit, for now. 🙂 It IS funny, though, to see me actually condone a loop in code whether one consideres it to be a RBAR process or not. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Jeff Moden (9/11/2011)
Florian Reischl (9/11/2011)
Ha! Logged for future generations! Jeff condones a RBAR solution 😀
Heh... I could argue all day why it's not RBAR but I'll take the hit, for now. 🙂 It IS funny, though, to see me actually condone a loop in code whether one consideres it to be a RBAR process or not. :hehe:

That discussion could actually lead to a lot of improvements. I could see the argument in a case where you want to rebuild more than 1 indexes on the table where you may want to multi-thread the process to lock the table for as short of a time as possible.

This assumes of course that you can allocate the ressources for the cpu, tempdb. Ram in theroy would not be affected because if you intend to rebuild the CI, then all the pages go to memory anyways. In that sense it could lessen the hit on some HW.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 9

Gianluca Sartori (9/9/2011)
You can start the job with sp_start_job and wait for its completion in a loop.
If the job exceeds the timeout you set, stop it with sp_stop_job.

But don't you have to wait for a rollback if it's part-way completed then?

Rich

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

rmechaber (9/12/2011)
Gianluca Sartori (9/9/2011)
You can start the job with sp_start_job and wait for its completion in a loop.
If the job exceeds the timeout you set, stop it with sp_stop_job.
But don't you have to wait for a rollback if it's part-way completed then?
Rich

Of course.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 11

Ninja's_RGR'us (9/12/2011)
rmechaber (9/12/2011)
Gianluca Sartori (9/9/2011)
You can start the job with sp_start_job and wait for its completion in a loop.
If the job exceeds the timeout you set, stop it with sp_stop_job.
But don't you have to wait for a rollback if it's part-way completed then?
Rich
Of course.

Point being, the o.p. then exceeds his allowable window for the job to "run" b/c the process is still consuming resources during the rollback.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

rmechaber (9/12/2011)
Ninja's_RGR'us (9/12/2011)
rmechaber (9/12/2011)
Gianluca Sartori (9/9/2011)
You can start the job with sp_start_job and wait for its completion in a loop.
If the job exceeds the timeout you set, stop it with sp_stop_job.
But don't you have to wait for a rollback if it's part-way completed then?
Rich
Of course.
Point being, the o.p. then exceeds his allowable window for the job to "run" b/c the process is still consuming resources during the rollback.

There's no easy solution for this. You can only guesstimate at it (with good precision, but not perfect). I've posted the best one I know of and didn't get a reply.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 13

Ninja's_RGR'us (9/12/2011)
rmechaber (9/12/2011)
Ninja's_RGR'us (9/12/2011)
rmechaber (9/12/2011)
Gianluca Sartori (9/9/2011)
You can start the job with sp_start_job and wait for its completion in a loop.
If the job exceeds the timeout you set, stop it with sp_stop_job.
But don't you have to wait for a rollback if it's part-way completed then?
Rich
Of course.
Point being, the o.p. then exceeds his allowable window for the job to "run" b/c the process is still consuming resources during the rollback.
There's no easy solution for this. You can only guesstimate at it (with good precision, but not perfect). I've posted the best one I know of and didn't get a reply.

Agreed, just pointing out to o.p. that this may not meet his requirements, if rollbacks interfere with his users. About the only thing I can think of is to check for job completion about 1/2-way through the allowable window and stop the job at that point, but that's not likely gonna help much.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

My method will go way more precise than this.

That code keeps history of previous work. So after a few runs, you can check the history to see avg run time for that particular index and then ajust with the current page count.

Assuming no changes on the ressources you should get an accurate estimate to within a few seconds.

Then you would't blow the window by more than a few seconds (if at all).

Also you can sort the work order and work only on the most critital / fragmented tables.