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
 BEGIN
  PRINT 'I am running a job - the time is: ' + CAST(GETDATE() AS VARCHAR)
 END
 

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
DECLARE myCursor CURSOR FOR
SELECT SYSINDEXES.NAME FROM SYSINDEXES
  INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSINDEXES.id
 WHERE SYSINDEXES.NAME NOT LIKE '_WA_Sys_%' AND
       SYSINDEXES.INDID > 0 AND 
       SYSINDEXES.INDID < 255 AND 
       SYSOBJECTS.NAME = 'myTable'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @myIndex
WHILE @@FETCH_STATUS = 0 AND GETDATE() < @FinishTime
 BEGIN
  PRINT 'Reindexing: ' + @myIndex
  DBCC DBREINDEX (myTable, @myIndex)
  FETCH NEXT FROM myCursor INTO @myIndex
 END
CLOSE myCursor
DEALLOCATE 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