Being into application support we check daily and weekly jobs schedule status that has been scheduled in SQL server in many servers for different projects. I wrote some queries to get the status as below.(if you have better solution please comment)
We can use a readymade solution SPHELP_JOBSCHEDULE store procedure which takes jobid, jobname, schedulename, scheduleid and schedule descirption. But this will return multiple columns and what if we have to query only the enabled status of a schedule.
So the other approach may be to query the MSDB table DBO.SYSSCHEDULES which will give us the 'enabled' column from which we can get the Schedule status of the job. For more information we can join the dbo.sysjobschedules and dbo.SYSJOBS.
SELECT * FROM DBO.SYSSCHEDULES WHERE [NAME]='JSCH_HR600d'
Earlier I have posted how to check the job execution status.
SQL Server Tips · · · ·
what we do is as and when a job succeed or fails...a mail will be triggered from sqlserver to dev team or DBA....its more maintainable I believe
@satyajit you are absolutely right. But as multiple servers are there we adopted that approach and are getting a consolidated mail for all server from a central machine.
@manas - based on your comment, do you use Central management server for consolidated email for all server status?
@Vanee not exactly we have some virtual machine and from that couple of scripts are running to check these status. I am not confident on the CMS :) also.