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.

Auto starting the sqlserver service

Hello all,
I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who somehow
got thrown into figuring out how to fix an issue on one of our production
servers. We have a machine that is regularly rebooted (this is a whole
'nother story) and at times when it comes back on it does not start the
sqlserver service.
I was wondering if anyone knew how to right a batch file that would
regularly check, say every 15min, to see if the service was running, and if
not to star it.
Any help or even a nudge in the right direction would be ever so appreciated
.go to start-->run-->services.msc-->look for MSSQLSERVER and double
click on that-->change the startup type to Automatic...|||It has been verified to be set at automatic. For some reason the
sqlserveragent does not start at bootup on occasion. If we go into the
services panel and right click "start" the service will come up fine. But
unfortunatly we do not know until the end user complains.
We are currently implementing MOM which may help us monitor when the service
is not running.
"Shadow" wrote:

> go to start-->run-->services.msc-->look for MSSQLSERVER and double
> click on that-->change the startup type to Automatic...
>|||Use this to check the sqlagent status:
exec xp_servicecontrol 'querystate', 'sqlserveragent'
If it returns "stopped" then start the service by:
exec master..xp_servicecontrol N'start', N'sqlserveragent'
"FranklinST_Admin" <FranklinSTAdmin@.discussions.microsoft.com> wrote in
message news:F6293761-0C54-4BB4-B6CD-390A8C27C584@.microsoft.com...
> Hello all,
> I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who
> somehow
> got thrown into figuring out how to fix an issue on one of our production
> servers. We have a machine that is regularly rebooted (this is a whole
> 'nother story) and at times when it comes back on it does not start the
> sqlserver service.
> I was wondering if anyone knew how to right a batch file that would
> regularly check, say every 15min, to see if the service was running, and
> if
> not to star it.
> Any help or even a nudge in the right direction would be ever so
> appreciated.

Auto starting the sqlserver service

Hello all,
I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who somehow
got thrown into figuring out how to fix an issue on one of our production
servers. We have a machine that is regularly rebooted (this is a whole
'nother story) and at times when it comes back on it does not start the
sqlserver service.
I was wondering if anyone knew how to right a batch file that would
regularly check, say every 15min, to see if the service was running, and if
not to star it.
Any help or even a nudge in the right direction would be ever so appreciated.
go to start-->run-->services.msc-->look for MSSQLSERVER and double
click on that-->change the startup type to Automatic...
|||It has been verified to be set at automatic. For some reason the
sqlserveragent does not start at bootup on occasion. If we go into the
services panel and right click "start" the service will come up fine. But
unfortunatly we do not know until the end user complains.
We are currently implementing MOM which may help us monitor when the service
is not running.
"Shadow" wrote:

> go to start-->run-->services.msc-->look for MSSQLSERVER and double
> click on that-->change the startup type to Automatic...
>
|||Use this to check the sqlagent status:
exec xp_servicecontrol 'querystate', 'sqlserveragent'
If it returns "stopped" then start the service by:
exec master..xp_servicecontrol N'start', N'sqlserveragent'
"FranklinST_Admin" <FranklinSTAdmin@.discussions.microsoft.com> wrote in
message news:F6293761-0C54-4BB4-B6CD-390A8C27C584@.microsoft.com...
> Hello all,
> I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who
> somehow
> got thrown into figuring out how to fix an issue on one of our production
> servers. We have a machine that is regularly rebooted (this is a whole
> 'nother story) and at times when it comes back on it does not start the
> sqlserver service.
> I was wondering if anyone knew how to right a batch file that would
> regularly check, say every 15min, to see if the service was running, and
> if
> not to star it.
> Any help or even a nudge in the right direction would be ever so
> appreciated.

Auto starting the sqlserver service

Hello all,
I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who somehow
got thrown into figuring out how to fix an issue on one of our production
servers. We have a machine that is regularly rebooted (this is a whole
'nother story) and at times when it comes back on it does not start the
sqlserver service.
I was wondering if anyone knew how to right a batch file that would
regularly check, say every 15min, to see if the service was running, and if
not to star it.
Any help or even a nudge in the right direction would be ever so appreciated.go to start-->run-->services.msc-->look for MSSQLSERVER and double
click on that-->change the startup type to Automatic...|||It has been verified to be set at automatic. For some reason the
sqlserveragent does not start at bootup on occasion. If we go into the
services panel and right click "start" the service will come up fine. But
unfortunatly we do not know until the end user complains.
We are currently implementing MOM which may help us monitor when the service
is not running.
"Shadow" wrote:
> go to start-->run-->services.msc-->look for MSSQLSERVER and double
> click on that-->change the startup type to Automatic...
>|||Use this to check the sqlagent status:
exec xp_servicecontrol 'querystate', 'sqlserveragent'
If it returns "stopped" then start the service by:
exec master..xp_servicecontrol N'start', N'sqlserveragent'
"FranklinST_Admin" <FranklinSTAdmin@.discussions.microsoft.com> wrote in
message news:F6293761-0C54-4BB4-B6CD-390A8C27C584@.microsoft.com...
> Hello all,
> I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who
> somehow
> got thrown into figuring out how to fix an issue on one of our production
> servers. We have a machine that is regularly rebooted (this is a whole
> 'nother story) and at times when it comes back on it does not start the
> sqlserver service.
> I was wondering if anyone knew how to right a batch file that would
> regularly check, say every 15min, to see if the service was running, and
> if
> not to star it.
> Any help or even a nudge in the right direction would be ever so
> appreciated.