SQLserveragent if failed how to setup alert

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

General SQL Server Forums

New to SQL Server Programming

SQLserveragent if failed how to setup alert

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2009-09-02 : 06:27:48

hi,
i have the option of Autostart Sqlserveragent when server restarts set, but sometimes when unexpected shutdown happens the service fail to start automatically. what is the way to be notified when this service fails to start?
best regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 06:32:10

see this

http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/7f24ed6a-8fdb-4e3d-a601-2e1577666857/

ann06
Posting Yak Master

171 Posts

Posted - 2009-09-02 : 06:48:27

they suggested to use third party tools, we can't do this using perfmon tool? or using sql server itself like executing a query which involves sqlqueryagent if it fails then it sends a mail using xp_sendmail for notification.

ann06
Posting Yak Master

171 Posts

Posted - 2009-09-03 : 04:06:38

I came with a simple solution, in case any fello want to use

i will use task scheduler to execute this

OSQL -E -d dbname -Q "exec agentalert" -o c:\results.txt

the stored proc agent alert will use the following

alter procedure Agentalert
as
declare @a int
exec @a= msdb.dbo.sp_start_job N'Insert new fax'
if @a<>0
begin
exec master.dbo.xp_sendmail 'baso@mail.com', 'Sqlserveragent on RPDHO07 not running, Please Check !!'
print 'Failed'
return

end
print 'Running'

if the Agent is not running sp_start_job will give an error and will send mail else its fine
i will schedule every day in the morning

Thanks i started to contribute to the community :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources