Does anyone have a handy job out there to check to make sure that the
sqlagent is running and if it isn't uses sql mail (xp_sendmail) to let you
know this. I have many alerts setup and they depend on the sqlagent running
to send me notifications. Since I am so dependent on the sqlagent I want
to make sure it is running (maybe checking once or twice a day). I know an
easy solution is to set up a sql job that sends me an e-mail once a day and
if I don't get that e-mail daily then the sqlagent or my exchange server is
down (Too many e-mails). I was hoping to find a better way where somehow
you can verify that the sqlagent is running and if it is not then send an
e-mail using sql mail.

any ideas would be appreciated.

Report this post to a moderator

You could set up a scheduled task to execute osql using
AT or the Scheduled Tasks wizard and specify and input
file to test for this e.g.

osql -E -S SERVERNAME -i c:\sqlagent.sql


and sqlagent.sql would contain something like

set nocount on

declare @status varchar(100)
create table #agent(status varchar(100))

insert #agent
exec master..xp_servicecontrol 'QueryState', 'SQLServerAgent'

select @status = status from #agent

if @status <> 'Running.'
begin

exec master..xp_sendmail 'admin@sql.com' , 'SQL Agent down'

end

drop table #agent
go


--
HTH

Jasper Smith (SQL Server MVP)

I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"GI" <glenn.illig@healthtrio.com> wrote in message
news:uhXZ1QKnCHA.2444@TK2MSFTNGP10...
> Does anyone have a handy job out there to check to make sure that the
> sqlagent is running and if it isn't uses sql mail (xp_sendmail) to let you
> know this. I have many alerts setup and they depend on the sqlagent
running
> to send me notifications. Since I am so dependent on the sqlagent I want
> to make sure it is running (maybe checking once or twice a day). I know
an
> easy solution is to set up a sql job that sends me an e-mail once a day
and
> if I don't get that e-mail daily then the sqlagent or my exchange server
is
> down (Too many e-mails). I was hoping to find a better way where somehow
> you can verify that the sqlagent is running and if it is not then send an
> e-mail using sql mail.
>
> any ideas would be appreciated.
>
>

Report this post to a moderator