Alert for SQLAgent Stopping?

Question

Alert for SQLAgent Stopping?

craig-404139

Hall of Fame

Points: 3274
More actions
March 26, 2007 at 11:05 am

#76678

So im trying to configure an alert to notify me via email when the sqlagent service stops (one of my servers it has been stopping for no apparent reason, even though auto restart is enabled, it stays stopped until i manually restart it) I tried to add an alert but since its just an information message in the event log, i cant seem to add it as an alert. Any ideas on how i can configure this? TIA

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

I use SQL Sentry to monitor SQL Agent and SQL Server itself from a different server to monitor and send emails alerting me. There are other products out there as well that can monitor Windows services and alert if any of them is not in the state you want them to be.

Troy Kelly SSC-Addicted Points: 480 More actions · Answer 2

I use the SQL Distributed Management Objects in a stored proc to get the status of SQL Agent and have the stored proc email me if the status is anything other than running. I have a vb app that executes the stored proc once an hour.

Troy Kelly

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

You should be able to fire off a task from the Service Applet. After xx failures, send an SMS message or email to yourself from there. Is this W2K3?

craig-404139 Hall of Fame Points: 3274 More actions · Answer 4

not this is a windows 2000 box. Im new to sql so im not sure how to setup any of the offered solutions. Anyone have a link or a little more detailed info?

Troy Kelly SSC-Addicted Points: 480 More actions · Answer 5

Here's the stored proc I use, I put it in the master database and then call it from a VB app. I'm sure you could get a developer to help you setup an application to execute the stored proc.

CREATE PROCEDURE [dbo].[Check_AgentStatus]

@user varchar(30), -- VALID SQL Server Login

@pwd varchar(30) -- VALID Login Password

AS

/*

Name: Check_AgentStatus

Description: Determines the status of sql server agent and emails if it's not running.

Parameters: @user - Sql server user login

@pwd - Sql server user password

Called By: CheckAgentStatus VB application

Returns: @source

@description

@status_msg

@status

Other Outputs:

History:

*/

-- Returned information

DECLARE @source varchar (255)

DECLARE @description varchar (255)

DECLARE @status_msg varchar(100)

DECLARE @status int

-- Internal variables

DECLARE @object int

DECLARE @hr int

DECLARE @from varchar(8000)

DECLARE @to varchar(8000)

DECLARE @subject varchar(8000)

DECLARE @message varchar(8000)

DECLARE @date as varchar(25)

-- Create SQLDMO (SQL Distributed Management Objects) Object

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT

SELECT Source=@source, <A href="mailtoescription=@description">Description=@description, status_msg = @status_msg, status = @status

RETURN

END

-- Call Method to connect to the server

EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @@ServerName, @user, @pwd

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT

SELECT Source=@source, <A href="mailtoescription=@description">Description=@description, status_msg = @status_msg, status = @status

RETURN

END

-- Get property value for the SQL Server Agent current status

DECLARE @property varchar(255)

EXEC @hr = sp_OAGetProperty @object, 'JobServer.Status', @property OUT

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT

SELECT Source=@source, <A href="mailtoescription=@description">Description=@description, status_msg = @status_msg, status = @status

RETURN

END

--Get status information to return

SET @status_msg = CASE @property WHEN 1 THEN 'SQL Server Agent is running'

WHEN 3 THEN 'SQL Server Agent is stopped'

WHEN 2 THEN 'SQL Server Agent is paused'

WHEN 6 THEN 'SQL Server Agent is in transition from paused to running'

WHEN 7 THEN 'SQL Server Agent is in transition from running to paused'

WHEN 4 THEN 'SQL Server Agent is in transition from stopped to running'

WHEN 5 THEN 'SQL Server Agent is in transition from running to stopped'

ELSE 'Unable to determine service execution state.'

END

SET @status = @property

-- clean up objects

EXEC @hr = sp_OADestroy @object

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT

SELECT Source=@source, <A href="mailtoescription=@description">Description=@description, status_msg = @status_msg, status = @status

RETURN

END

-- if no errors return status information

SELECT Source=@source, <A href="mailtoescription=@description">Description=@description, status_msg = @status_msg, status = @status

--Send email if sql server agent not running

IF @status <> 1

Begin

set @date = getdate()

set @from = @@servername

set @to = 'Your email address here'

set @subject = 'Sql server agent problem.'

set @message = 'Sql server agent is not running on this server ' + @@SERVERNAME + ', please restart.'

exec ppc_sqlmail @from = @from, @to = @to, @cc = @cc, @subject= @subject, @message = @message

End

GO

Troy Kelly

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 6

For a windows alert, you can go into the services applet in control panel. Pick the SQL Server Agent service and get properties. Go to the recoveyr tab. There you have options for restart and for failures. You can run a file on failure, so you can put a net send or an email program into a batch file and pick that to run on failure or stopping.