To ensure the systems I am responsible for have as much up time as possible we have a very simple monitoring service which monitors all critical systems and notifies of any failures via email and SMS. One of the systems I am responsible for receives daily data (CSV format) from multiple business units, during the week it is considered a critical failure if a file from a business unit does not land. This is less true on weekends as some business units do not work and thus if their system fails and does not generate a file my system throws a subsequent failure (domino effect) even though the generation of this file is out of my control. So to counter this problem I crafted what I consider a rather elegant solution.
The monitoring service I referred to above has many ways of monitoring everything from Windows services through to individual SQL server jobs. Although the jobs that load the files have a designated start time the files land sporadically throughout the day and as such I created a table that holds the jobs I wish to monitor and a stored procedure which monitors the status of those jobs through sysjobs and sysjobsteps (thus if any one of the steps fails [some steps rely on each other] I am notified). The monitoring service throws an error if the stored procedure returns any rows and subsequently sends an SMS with a description etc etc.
So to stop the service reporting a false negative on weekends I added a column to the table which holds the jobs I want to monitor – DayOfWeekExclusion | varchar(16). This column holds a comma separated list of values which represent the numeric days of the week. I then use the DATEPART function with the dw parameter and hey presto no more SMS notifications for the non critical jobs on the weekend, pseudo SQL below:
SELECT JobName FROM JobsToMonitor
WHERE DayOfWeekExclusion NOT LIKE
'%' + CAST((SELECT DATEPART(dw, GETDATE()))AS VARCHAR(1)) +'%')
Cheers
David