Automatic Job Monitoring

I'm a SQL Server 7/2000 DBA and manages about 40 servers in different networks. Every morning I check through the Enterprise Manager if all Jobs (backup, maintenance, etc.) have run successfully. This check costs me 1 hour per day.

Because of a reorganization I've got some new college's and lost some college's. My new college's think this is to much work, so it should be automated. They only want the failed jobs to report an error on a website or something like that, and don't want to check 40 servers. I don't agree in this, because I'm affraid I'm going to miss some errors.

How do you do your checks every morning?

Thanks,

MarcoYou can look up master SQL Server Agent job in SQL BOL. I personally have never used it except for a long time ago when I was experimenting with it early on.

You can also use SQL Agent Mail to flag when a job has failed and send out an alert. SQL Agent Mail is a pain to set up (have to install Outlook), is not fully supported by MS in an clustered environment (Outlook is not cluster aware) and when it goes South in can really go south in a bad way.

I personally manage about a dozen SQL servers. I use a combination of SQL Agent mail on a "management" SQL server (to which all the other servers forward their alerts) and a web site that pulls from a database on the "management" database. This database is in turn populated by a DTS package that pulls from the sysjobhistory table in the other servers. It was a fair amount of work to set up, but it's been running for almost three years now with very few changes.

Regards,

hmscott|||u can build query on system tables for job history in msdb database like : sysjobs, sysjobhistory, sysjobschedules. to make it automated, just schedule the query, then send a notification email, or store to a table, or however you want.
to make a complete check, notice the schedule, coz only already run steps (succeeded, failed, or cancelled) are logged in sysjobhistory.