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