Script to mail when the sql server agents stops

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Script to mail when the sql server agents stops

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-09-05 : 12:03:57

Hello

I would like to have a script which mails the dba mail box when the sql server agent stops running. I am using the SMTP server for mailing.

I am using the query :
EXECUTE xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'

to check the status.

How do I change it or write a script to mail when it fails.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 12:51:40

Do you plan to manually run this stores procedure every five minutes?

Peter Larsson
Helsingborg, Sweden

vaddi
Posting Yak Master

145 Posts

Posted - 2006-09-05 : 13:07:42

Hi

Yes, I would like to run this procdure every 10 to 15 minutes.

The mailing that I am using is :

EXEC master.dbo.xp_smtp_sendmail
@FROM = N'testsql2000@is.depaul.edu',
@TO = N'dvaddi@depaul.edu',
@server = N'smtp.depaul.edu',
@subject = N'Status of sqlserver!',
@type = N'text/html',
@message = @message

So, I need to know..how to fire this..when the sql server agent is not running.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 13:09:13

You have to set this up on another SQL server.
And likewise, you have to set this up on the first server, watching the second server.

Peter Larsson
Helsingborg, Sweden

vaddi
Posting Yak Master

145 Posts

Posted - 2006-09-05 : 13:19:19

Sorry but I didnot get actually what you were saying.Please can you elaborate.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 13:24:12

If you want this done in SQL, you must have 2 servers. Server A and Server B.

You must have Server A watching Server B, and you must have Server B watching Server A.

You can also make use of WMI, and have a scheduled task checking the service.

Peter Larsson
Helsingborg, Sweden

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-09-07 : 15:19:28

Vaddi,

What Peso is saying is that if you have a job on Server 1 and the Job Agent on Server 1 goes down then the script will no longer run because the Job Agent won't be running in order to run it. What you'll have to do is have a job running on Server 2 that monitors Server 1 so that if the Job Agent on Server 1 goes down, then Server 2's job would catch it. Likewise, you could have a job on Server 1 that is monitoring the job agent on Server 2 then.

If you know how to write VB or C++ code etc to make an executable you could write your own application outside of SQL that would do the monitoring for you.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-09-07 : 21:19:11

use a batch file or vbscript or whatever programming language you have, run it using the windows scheduler on the sql server machine

or at the OS level, use net start [servicename]

hope this helps...

--------------------
keeping it simple...

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-07 : 22:03:07

you could also just set the sqlagent to automatically restart if it ever stops.

-ec

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-07 : 22:16:55

I do a check via OSQL to verify that SQL Server is up an runnimg and the SQL Server Agent is running. Basically, if SQL Sever is not up, OSQL will return an error. I use the query below to verify that the agent is runnimg.

It's easy to run via xp_cmdshell, and xp_cmdshell will return non-zero in the return code if OSQL cannot connect. If it can connect, all you have to do is parse the xp_cmdshell output to verify the agent is running.

If the SQL Server Agent is running, this query will return a 1.


select Agent_running = case when count(*) > 0 then 1 else 0 end 
from master.dbo.sysprocesses
where  program_name like 'SQLAgent%'

CODO ERGO SUM

vaddi
Posting Yak Master

145 Posts

Posted - 2006-09-08 : 11:30:58

Thanks for the messages.

Can I link the result ,so that I can receive a mail. And do I need to run that script in command line or some where else.

Sorry for bugging you guys too much. But I have no idea of how to work on it.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources