SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Controlling Jobs

By Joe Doherty,

Have more control over your jobs

Ever thought about scheduling a job to run within a particular timeframe? For example, you want to run your index maintenance tasks overnight but you don't want the job running over into the timeframe when your morning backups start.

I had an issue recently where I needed to run a huge indexing task against my databases. I estimated this would have taken a few days to complete and therefore caused me problems due to the various other processes running on the servers throughout the day. I wanted to be able to run my job but only had a couple of time slots available, and obviously wanted to automate as much as possible.

Ideally I needed to be able to configure a new SQL job than ran between 2am and 5am, as well as 10pm and 12am. Although there are ways to terminate jobs at a given time I doubt SQL would have appreciated this forceful approach to managing its jobs. Unfortunately SQL job manager isn't aware of what is going on inside its jobs and therefore cannot terminate gracefully when certain criteria are met. I racked my brain convinced that there was something available within SQL that would take care of this. However...

SQL 2000 doesn't provide an easy mechanism to achieve this. What I intend to show you here is a simple way to configure your jobs to run within a particular timeframe. If the job is still running at the time you have configured other processes then it will simply end the job.
There are (as always) a number of different ways to achieve this, as well as some limitations to my particular example, but the aim of this document is to share an idea about one of the possible ways you can have more control over the length of time your jobs run for.

Basically the script that we are going to put together periodically checks the current time and compares it to the time you have specified you want the job to stop.

Here goes...

Step 1: Here we simply declare the variables that we'll need in the script.

-- Declare variables
DECLARE @TerminateAfterMins INT -- this variable stores the amount of time 
                               -- (in minutes) you want your job to run for
DECLARE @FinishTime DATETIME -- this variable stores the new time that your job should stop

Step 2: Here we are going to manually configure the variable @TerminateAfterMins with the number of minutes we want the job to run for. Let's set our job to run for 10 minutes for the sake of this demonstration.

-- Manually set the number of minutes to run job for
SET @TerminateAfterMins = 10

Step 3: This is where we calculate the actual time we want our job to terminate gracefully. This value is stored in @FinishTime.

-- Calculate the new time after the required minutes have been added
SET @FinishTime = DATEADD (mi, @TerminateAfterMins, GETDATE())

Step 4: You can print both the start time and intended finish time to screen if you wish.

PRINT 'Current time is: ' + CAST(GETDATE() AS VARCHAR)
PRINT 'Time to finish processing: ' + CAST(@FinishTime AS VARCHAR)

Step 5: This is where you insert your tasks. The tasks are continually run until the @FinishTime is reached.

-- Perform function(s) until the required time is reached
WHILE GETDATE() < @FinishTime
  PRINT 'I am running a job - the time is: ' + CAST(GETDATE() AS VARCHAR)

And that's all there is to it really! That's the basic structure making use of the extremely useful DATEADD function.

Now, due to the nature of the way the script works, it does rely on a periodic check of the current time and compares it to the time that we stored in @FinishTime. If the logic finds that the current time >= to the @FinishTime it will end the WHILE loop, and therefore end the job.

This doesn't mean that you can insert a DBCC DBREINDEX (authors, '', 70) command in to the WHILE loop and expect it to get on with rebuilding as many indexes as it can for the next 10 minutes. You have to be a bit more imaginative than that.
What we can do is create a CURSOR that selects all the indexes in a particular table. Then using the WHILE loop check for time and reindex each index in turn.

Here's an updated Step 5: to illustrate my point:

-- Perform function(s) until the required time is reached
       SYSOBJECTS.NAME = 'myTable'

OPEN myCursor
FETCH NEXT FROM myCursor INTO @myIndex
  PRINT 'Reindexing: ' + @myIndex
  DBCC DBREINDEX (myTable, @myIndex)
  FETCH NEXT FROM myCursor INTO @myIndex
CLOSE myCursor

* note that you would also need to DECLARE @myIndex VARCHAR(100) in Step 1:

Running the above will scroll through the indexes in the table you specify and reindex them. Providing they take longer to reindex than the 10 minutes we have defined as the job run time you should find that the job terminates only having reindexed some of the indexes.

You might be thinking that this job is a complete waste of time seen as it potentially only reindexes a portion of the indexes each time it is run. And you are right, however I am simply going through the thought process I went through when trying to come up with something to control my jobs.

In the end I changed the routine within the WHILE loop to check the fragmentation levels of each index and, if necessary, perform a defrag or reindex. This way every time it is run it gets further and further. Eventually, by running the job twice per day over a few days, my indexing task became less of a burden on my servers as it was spread out over the week and I was able to automatically and dynamically maintain my indexes within desired time slots.

In the example above we are reindexing. If we specify that the job should run for 10 minutes and one of the the indexes is huge then potentially it may run for a lot longer. You should bear this in mind when configuring your jobs. Perhaps schedule the job to finish an hour prior to your other processes starting.

And remember you will have to create a job and schedule it to run the above stored procedure. I have attached the full script configured to create a stored procedure that accepts a parameter. This parameter is the number of minutes the job should run for.

Play around with the script and see what you can come up with. You may find it has some limitations depending on what it is you want to do. You can change the parameters of the DATEADD function so that it works in seconds, hours and days.

I hope this has been of use to you.

Download usp_TerminateAfterMinutes.sql

Total article views: 8297 | Views in the last 30 days: 7
Related Articles


why reindex??


Reindex all indexes online or offline depending on day of week.

This script will reindex (online) any index eligible to be reindexed online. Accepts a day of week...


Reindexing & defragmentation

Reindexing & defragmentation






Reindexing tables with less than 1000 pages