Sunday, May 27, 2012

Scheduling a SQL Agent Job to Run on a Calculated Day that isn’t included in the Regular Scheduler Options

I’ve had a hard time coming up with a title for this post, because while the concept is easy to understand, it’s difficult to summarize.
We refresh one of our development instances on the first Monday of each month.  That’s an easy thing to schedule, as it’s an option within the SQL Agent scheduler:
image
We want to send a notification to the affected users beforehand, however, and since the refresh occurs in the wee hours of the morning, we can’t send out that alert on Monday.  It has to occur on the Friday before the first Monday. 
That, friends and neighbors, isn’t a built-in option.
It’s a little trickier (though in the end, not terrible) because the Friday before the first Monday could be in this month or in next month.


So what to do?  One good solution is to schedule a job to run every Friday.  It checks to see if today is the Friday before the first Monday, and if it is, it sends an email.
Here’s that the script looks like that accomplishes this:
DECLARE @msgbody VARCHAR(500)      
DECLARE @emaillist VARCHAR(100)       
DECLARE @subj VARCHAR(100)      
DECLARE @FONM DATETIME -- FirstofNextMonth       
DECLARE @dateofrefresh VARCHAR(50)
SET @emaillist = 'NotifcationList@mymail.edu' --Email recipients, semicolon separated       
SET @FONM = DateAdd(Month, 1 + DateDiff(Month, 0, GETDATE()), 0) --First day of the next month       
SET @dateofrefresh = CONVERT(VARCHAR, DateAdd(day, (9 - DatePart(weekday, @FONM)) % 7, @FONM), 101) --First Monday of next month
/* @dateofrefresh is the 1st Monday of the month. 
    The IF statement below checks to see if 3 days from today is the first Monday.       
    If it is, the refresh will happen on Monday, so we send the email.       
*/    
IF CONVERT(VARCHAR, dateadd(day, 3, getdate()), 101) = @DateOfRefresh -- Friday is 3 days from Monday, so if these match, send the email       
BEGIN       
    SET @subj = 'DB refresh scheduled for ' + @dateofrefresh      
    SET @msgbody = 'Your DB is scheduled for refresh on ' + @dateofrefresh + '.'       
    + CHAR(13) + CHAR(10)      
    + 'Please ensure that all of your data has been saved, as it will be overwritten by a copy of production.'
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'email_profile'
         ,@recipients = @emaillist      
        ,@subject = @subj      
        ,@body = @msgbody     
END 

 

Working through the rationale

The first thing we do is find the first day of next month, as we’ll do our following calculation based on that:
SET @FONM = DateAdd(Month, 1 + DateDiff(Month, 0, GETDATE()), 0)

The inner part of this:
DateDiff(Month, 0, GETDATE())
returns the number of months between date zero (1/1/1900) and the first of this month.  The outer DateAdd piece adds one month to that number, which is the first of next month.

Once we’ve got the first day of next month, we need to find the closest Monday to that date:
CONVERT(VARCHAR, DateAdd(day, (9 - DatePart(weekday, @FONM)) % 7, @FONM), 101) 

The inner-most part of this line
DatePart(weekday, @FONM)
returns the integer 1 – 7 corresponding to Sunday – Saturday.

The next piece 
DateAdd(day, (9 - DatePart(weekday, @FONM)) % 7, @FONM)
Does the actual math.  What we’re doing is subtracting the datepart integer from 9, which gives us the number of days between the first of the month and the next Monday.  The %7 forces SQL server to use Sunday as the first day of the week, so we don’t have to worry about systems with a non-default @@datefirst setting.

Once we have the date for the first Monday of next month, we’ll check and see if today is three days from that date.  If it is, then this is the Friday before the first Monday, and we send our email.  If it isn’t, we simply end, doing nothing
IF CONVERT(VARCHAR, dateadd(day, 3, getdate()), 101) = @DateOfRefresh -- Friday is 3 days from Monday, so if these match, send the email    
BEGIN    
    SET @subj = 'DB refresh scheduled for ' + @dateofrefresh     
    SET @msgbody = 'Your DB is scheduled for refresh on ' + @dateofrefresh + '.'    
    + CHAR(13) + CHAR(10)   
    + 'Please ensure that all of your data has been saved, as it will be overwritten by a copy of production.'   
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'email_profile'         ,@recipients = @emaillist         ,@subject = @subj         ,@body = @msgbody

3 comments:

  1. I learn some new stuff from it too, thanks for sharing your information. book me today

    ReplyDelete
  2. i was just browsing along and came upon your blog. just wanted to say good blog and this article really helped me. bookmetoday.com

    ReplyDelete
  3. Grand Casino Resort, Reno, NV - Mapyro
    Grand Casino 성남 출장안마 Resort is located in 안동 출장샵 Reno, NV. The casino is open 24 hours and offers live entertainment for 과천 출장안마 guests in NV. The casino is 제주도 출장마사지 located in the 성남 출장샵 centre

    ReplyDelete

Thanks for leaving a comment!