sp_update_job and Editing System Tables.

The initial problem: We have a particular situation that involves some scheduling. At the end of the month, we need to do some additional ETL processing for financial reporting. At that time, we want to avoid competing for resources with the other, scheduled jobs that would normally run during that time of day.

The first solution: So that we don’t compete for server resources with other jobs, we create a job that disables jobs at the end of the month, and re-enables them after the month-end processing is done.

IF DATEPART(day,
    DATEADD(day,1,CURRENT_TIMESTAMP)
   ) = 1 /* If tomorrow is the first of the month */
BEGIN
  UPDATE msdb.dbo.sysjobs
  SET enabled = 0
  WHERE name = 'Daily ETL Master'
END

The second problem: After scheduling the above code to run on the last day of the month, we open up the Job Activity Monitor and see that the jobs are disabled. However, we also notice that the jobs have run on schedule, despite being disabled. What happened?

The diagnosis: Two things went wrong here.

First, note that when the SQL Server Agent starts up, it reads the scheduled task information in from the sysjobs table, and caches it. In order to know that the system tables have changed, we need to notify the Agent that the cache needs to be refreshed. By now, we should be thinking stored procedure, instead of single statement. This is good, because SQL Server already supplies a stored procedure to do just that.

sp_update_job will take several parameters, one of which is @enabled. This will either enable or disable the job, when set to 1 or 0, respectively. The procedure will also cause the Agent to refresh it’s cache, if a cached parameter has been updated. The enabled flag is cached, so in this case, the code should read like this:

IF DATEPART(day,
    DATEADD(day,1,CURRENT_TIMESTAMP)
   ) = 1 /* If tomorrow is the first of the month */
BEGIN
  EXECUTE msdb.dbo.sp_update_job
    @job_name = 'Daily ETL Master',
    @enabled = 0
END

Now the job’s status will be updated, and the agent’s cache will be refreshed appropriately.

Second, and this is even more important, since we’ve scheduled this code to run on the last day of the month, is there a time when the IF statement would ever evaluate false? Nope. We’ve written an IF statement that will only ever evaluate one way. Which means, this IF statement isn’t even necessary in this case.

The final solution: This job only needs to run twice; on the last and first days of the month. The SQL Server Agent lets us define both of those schedules. So, we can create two job schedules, “Last Day of Month”, and “First Day of Month”, and assign those to a job that runs the following:

IF DATEPART(day,CURRENT_TIMESTAMP)= 1 /* First of the month */
BEGIN
  EXECUTE msdb.dbo.sp_update_job
    @job_name = 'Daily ETL Master',
    @enabled = 1
END
ELSE 
BEGIN
  EXECUTE msdb.dbo.sp_update_job
    @job_name = 'Daily ETL Master',
    @enabled = 0
END

Thanks for reading.

-David.

Advertisements

Please Comment

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

WordPress.com Logo

You are commenting using your WordPress.com 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