Monitoring Scheduled Jobs

Question

Monitoring Scheduled Jobs

Glenn Owens

SSCrazy

Points: 2099
More actions
April 9, 2007 at 3:44 pm

#69729

I realize that this may sound a bit odd but...
I have a large number of SQL Servers with 400+ databases and hundreds of scheduled jobs. I've got a number of monitoring tools which do a good job of providing informaiton about the sql jobs and their execution status.
The problem is... there is nothing to tell me that a job didn't run.
Is there a tool, script, etc the could provide not only the execution status of a job but could also tell me if a job didn't execute (based on the semi-dynamic daily job schedule)?
I've been bitten by this a couple of times and I'd really like to close this monitoring loophole.
Thanks
Glenn

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

I'm not positive if it will monitor jobs that just didn't run, versus jobs that failed, but you might want to try SQL Sentry (there is a free trial available, apparently). Note, I don't use the product (I'm slightly familiar with it only because I have looked into consolidating job monitoring), I don't work for them, and I get paid nothing for this.

Chuck Farley SSC Journeyman Points: 99 More actions · Answer 2

If you're considering third party tools, then SQL Farms has a tool that allows you to query all your databases and servers into a single result set, so you can get info from msdb databases for all your jobs (status, history, etc.) on all servers.

Glenn Owens SSCrazy Points: 2099 More actions · Answer 3

Thanks for the suggestions. However, we're currently using three monitoring tools: Idera's Diagnostic Manager (very nice/expensive tool), SQL Stripes (outstanding tool for full enterprise monitoring - bery inexpensive), and MOM 2005.

Each of these does a nice job of aggregating informaiton into a centralized repository - but none can tell me when a schedule job didn't run.

I'm going to start writing a script today that will provide a fully enoumerated schedule of jobs on each server (that will be refreshed once a day), then I'll write a couple of stored procedures that I'll add to my SQL jobs as start/stop steps, This won't be too bad as all of my backup/maintenance jobs are scripted from a central database. The trick will be to get the stored start/stop procedures to write to the central "job Status" database.

Glenn

David McFarland SSChampion Points: 11815 More actions · Answer 4

The one I linked to claims to have 77 conditions, including "Start/Finish", and then as automated actions you could send an e-mail, start the job, etc, but home-grown works if you have the time.

Glenn Owens SSCrazy Points: 2099 More actions · Answer 5

I was going to look at SQL Sentry today also. The problem that I've found with some of these tools is that you pay on a SQL instance or server and it gets quite expensive. Then, as we did, you cut back so that only "critical" servers are monitored leaving a non-homogeneous environment to support.

But I'll take a look...

Thanks

Glenn

Steve Jones - SSC Editor SSC Guru Points: 736430 More actions · Answer 6

I've heard great things about sqlSentry and people do seem to love it, but it can get expensive.

Reporting a negative is something I've done on a homemade exception report. I scan for jobs, backup files, etc. and if something is out of date, meaning that I see a file or a job that hasn't run in 24 hours, I raise a flag on an email delivered to me every morning. That way I know to check things. I haven't bothered to decode a schedule from the system tables yet and weekly jobs show up, but I know to ignore them. However it shouldn't be too hard to do, determine if something should have run, and then report on it.

Chuck Farley SSC Journeyman Points: 99 More actions · Answer 7

Glenn,

On another note- if you can provide more info about your exact need, I'd be happy to assist you in writing your SQL script that will return the info you are looking.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 8

I've had similar problem in the past and solved it by writing my own. Basically what I did was assemble a list of "must run jobs" that I scanned and sent an email every morning with their last run status. This got it down to a manageable list of jobs, not to mention a single email. I found that trying to get an email from every job that finished I would miss jobs that never ran, or that was running long (sqlSentry does a great job on this latest one).

Glenn Owens SSCrazy Points: 2099 More actions · Answer 9

Since I'm planning to record the job execution status (success, fail, and "did not run") in a database table - I'll plan to write some SQL Reporting Service reports to quickly identify jobs issues across the enterprise. I already have Email notification of job failures. I suspect that I can write an agent against the central Job Status database that could email me about the jobs the don't run (good idea).

Glenn

Chuck Farley SSC Journeyman Points: 99 More actions · Answer 10

In case it helps, I found this online:

http://education.sqlfarms.com/ShowPost.aspx?PostID=58

Anders Pedersen SSChampion Points: 11410 More actions · Answer 11

I just checked with SQLSentry, you can do the following with it regarding missed jobs:

Notify you abuot missed runs

And "Run missed" jobs when connection is offline.

v-427204 Newbie Points: 5 More actions · Answer 12

SQL script send failed jobs

------------------

EXEC master..xp_sendmail @recipients = 'mailaddress',

@query =

'select t1.run_date, t2.name, t1.step_name, t1.message from msdb..sysjobhistory t1 inner join msdb..sysjobs t2 on t1.job_id = t2.job_id

where run_status = 0 and run_date >= DATEPART(year, GETDATE()-1)*10000+

DATEPART(month, GETDATE()-1)*100+DATEPART(day, GETDATE()-1)',

@subject = 'Servername Failed Jobs',

@message = 'Here are Servername Failed Jobs',

@no_header='TRUE'

--@attach_results = 'TRUE'

--@width = 250

------------------------------------------

Glenn Owens SSCrazy Points: 2099 More actions · Answer 13

Thanks for the great postings!

I'm in the process of developing a Stored Procedure that will create an expanded/enumerated list of all Schdeul points for SQL Jobs on a server. I'm going to store the list in a centralized table. This table will become the daily schedule "target".

As each job in the enterprise executes - it will make an entry into a JobExecution status table. With this schema it should be fairly simple to identify job status: success, fail, did not run.

If anyone has developed (or knows of) a script to create the "target" list I'd appreciate your input.

Thanks again!

Glenn

Peter Shire SSC-Addicted Points: 457 More actions · Answer 14

Hi Glenn,

I work for SQL Sentry, Inc. Have you tested our Event Manager software and most importantly have you considered our Standard Edition? It has the monitoring and alerting functionality you mentioned and our Quick Start Packs include licenses to monitor SQL Server, Task Scheduler plus the 1st year of Annual Software Maintenance for about $300 per server. Not only will you get peace of mind from not having to worry about alerting but also total insight and control over your scheduled jobs and tasks.

Please read the SQLServerCentral review by Brian Knight here. Only a few paragraphs are specific to the Enterprise Edition: http://www.sqlservercentral.com/columnists/bknight/areviewofsqlsentry25.asp FYI...Brian reviewed an older version back when we still used our company name to refer to the product. We redefined our brand away from the camel case format to "SQL Sentry" and named our product "Event Manager" to distinguish it from the company.

Brian said that our software saved him an hour per day. I hope we can do the same for you.

Best Regards

Peter