Adding controllability to a low-reoccurrence scheduled maintenance job

In the practice of database management, there are situations where a maintenance job has to execute only once every week, 2 weeks, a month or even some longer duration.

In addition to its low reoccurrence, other restrictions may also apply to the job:

  • One successful execute is a must over the predefined time period on a specific date (For example, the last day of each month if it is a monthly job).
  • Repeating execution is forbidden, once the job has been successfully executed in a given period. Or it may cause adversary impact(s).
  • Recoverability is required. The scheduled maintenance job may not run according to the schedule, a DBA, therefore, may need be able to run a make-up execution of the job at a later time. There are a few possible reasons for recovering job runs. 
    — One case is that the RDBMS crashes so whether an internal or an external scheduler can not start-up the job;
    — Second scenario is that the external enterprise scheduler fails to initiate the maintenance job, because of a scheduler issue or connection the database broken; 
    — Still another case is that the underlying process failure caused the job unable to run successfully. Alerting is necessary as the supporting person need to know when the job went failure.
  • Ease of use. In case of a re-run is needed, the DBA manual procedure should be easy to follow and the result should be guaranteed.

To meet the needs outlined in the above bullet sections, some extra scripts need to be devised, and then executed first before the real maintenance job components start. With this controlling script as an introductory part of expanded job scripts, the job will behave exactly what it is expected.  

The same will work well for a job with multiple steps. The only change is that the new controlling script will be added to each step of the same job. By doing so, one can be sure the whole job will perform exactly as planned.

One practical tip is to use a stored procedure for each step for ease of management and efficiency of the job tasks.

Following is one example of adding control to an existing maintenance job which has 11 steps. As described in the foregoing section, the control part is added to each of the 11 steps as the first part to be executed (only step 1 is used for demonstration). The job is named “Maintain_Partition” and is supposed to run on the last day of each month:


CREATE PROCEDURE Proc_Maintain_Partition_step1

@Mode char(1) = ‘A’   — use ‘M’ as the parameter if a DBA want

— to run this step manually


       DECLARE @LastRun Datetime   — The default date to run the job 

       DECLARE @RightDay bit   — The default date to run the job 

       DECLARE @JobRun bit  — The same step run no more than once the same day


       IF (DATEPART (MONTH, getdate()) + 1  = DATEPART(MONTH, getdate() + 1 ))

              OR ((DATEPART (YEAR, getdate()) + 1 ) = DATEPART(YEAR, getdate() + 1 ))

              SET @RightDay = 1  — If the last day of a month


              SET @RightDay = 0  — not the last day of a month


IF @RightDay = 1

BEGIN  — the right day for the job, but need to make sure it has not executed


              LEFT(CONVERT(VARCHAR(8), H.RUN_DATE), 4) + ‘-‘ +

              SUBSTRING(CONVERT(VARCHAR(8), H.RUN_DATE), 5, 2) + ‘-‘ +

              RIGHT(CONVERT(VARCHAR(8), H.RUN_DATE), 2) + ‘ ‘ +

              LEFT(RIGHT(‘00000’ + CONVERT(VARCHAR(6), H.RUN_TIME),6) ,2) + ‘:’ +

              SUBSTRING (RIGHT(‘00000’ + CONVERT(VARCHAR(6), H.RUN_TIME),6), 3,2)

+ ‘:’ +

              RIGHT(RIGHT(‘00000’ + CONVERT(VARCHAR(6), H.RUN_TIME),6) ,2)))


                     ON H.JOB_ID=J.JOB_ID

       WHERE J.NAME = ‘Maintain_Partition’ AND H.RUN_STATUS = 1 

— The latest time the job ran


IF DATEDIFF (dd, @LastRun, getdate()) = 0 SET @RightDay = 0



— !!! Now two choices allowed:

     1) By default, it is Auto run ‘A’, on the right date and has not run yet, or

  2) a DBA needs to run manually with option ‘M’ by “exec Job_Step_ProcN ‘M'”

IF ( @Mode = ‘A’ and @RightDay = 1 ) OR Upper(@Mode) = ‘M’



              Place the real maintenance job step scripts here




       RAISERROR (‘Please do not run this job now’, 16, 1)


This entry was posted in Blogroll. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s