Query to identify if a script which was scheduled to run today successfully completed

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  October 22, 2014 at 2:57 pm

  #312468

  Hi all,

  Before I spend the next several hours working on a script to accomplish my objectives, I figured I'd make a post here and see if anyone is already aware of one which exists that would satisfy my needs, or which could be adapted relatively easily to do what I am trying to do.

  Our system has a whole slew of scripts which run on a daily basis, some of which are necessary for others to run successfully. We are trying to put in place a system of checks and balances, and some reports, to ensure that the scripts execute correctly.

  For now, we have a table which stores the scripts which we want to track, as well as the job schedule which executes the scripts. We also have another table which stores whether the script ran successfully for a given day.

  What I'm trying to do is tie these two pieces of information together. Basically, at the end of the day, what I'd like to see from the script, is an output that said:

  1) If a script which was scheduled to run today did not run (IE, no record in the table which stores the script run status)

  2) If a script which was not scheduled to run today did run (IE, a record in the above table, but no corresponding job scheduled to run today)

  I know that I can use the MSDB.dbo.sysschedules table to get all of the relevant information, so I'm already in the process of putting the query together, but it would be a huge timesaver if someone knew of a script which already did what I'm trying to do.

  Thanks!