Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Agent Stopped Expand / Collapse
Author
Message
Posted Thursday, May 23, 2013 4:17 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:16 AM
Points: 576, Visits: 542
if not exists (select * from [master].dbo.sysprocesses where left(program_name,8)='SQLAgent')
Begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='Database Mail',
@recipients ='email@id.com',
@body ='Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.',
@subject ='SQL Agent Stopped,Please Check' ;
End

I am using above script to find out whether SQL Agent Stopped is stoped or Running , I want to add IP address and Server name in subject line.

And I also want to monitor it for 40+ servers please help me for how above script can be used for monitoring the same .
Post #1455926
Posted Thursday, May 23, 2013 4:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:17 AM
Points: 1,379, Visits: 2,687
Something like this to get server name and IP

declare @subject nvarchar (100)
declare @v nvarchar(50)
set @v = convert (nvarchar (50),case when CONNECTIONPROPERTY('local_net_address') is null then '' else CONNECTIONPROPERTY('local_net_address') end)
print @v

set @subject = 'SQL Agent Stopped, Please Check ' + @@SERVERNAME

print @subject

Probably a better way of doing it, this just just off the top of my head
Post #1455935
Posted Thursday, May 23, 2013 5:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:16 AM
Points: 576, Visits: 542
Thanks for your reply it worked on sql server 2008, but
on sql server 2005 it is giving me following error

Msg 195, Level 15, State 10, Line 4
'CONNECTIONPROPERTY' is not a recognized built-in function name.
Post #1455945
Posted Thursday, May 23, 2013 5:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:17 AM
Points: 1,379, Visits: 2,687
Use the local_net_address column of sys.dm_exec_connections
Post #1455948
Posted Thursday, May 23, 2013 5:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:16 AM
Points: 576, Visits: 542
Thanks for your reply ,
Post #1455955
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse