Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
 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

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

Reindexing..

why reindex??

SCRIPT

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...

FORUM

Reindexing & defragmentation

Reindexing & defragmentation

FORUM

DBCC REINDEX

DBCC REINDEX

FORUM

Reindexing

Reindexing tables with less than 1000 pages

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones