How to monitor if SQL Server Agent service is up?
-
How to monitor if SQL Server Agent service is up? I would like to automate this, for example - email me if it is stopped. In the past we had incidents, when SQL Server Agent was manually stopped and some required jobs where not run, which caused problems for downstream systems.
-
You may need to create a DTS package and a VB script
below is using WMI to detect and start the Service.
' =======================
'Local Server
strComputer = "."
sServiceName ="SQLServerAgent"
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & _
strComputer & "\root\cimv2")
Set colListOfServices = objWMIService.ExecQuery _
("Select * from Win32_Service Where Name ='" & sServiceName & "'")
For Each objService in colListOfServices
WScript.Echo "Name: " & objService.DisplayName & vbCrLf & _
"State: " & objService.State & vbCrLf & _
"Start Mode: " & objService.StartMode
' -- Stop the Service --
' objService.StopService()
' -- Delay time for service to stop.
' Check service running
If objService.State = "Stopped" then
WScript.Echo "Service Name '" & sServiceName & "' has stopped"
' -- Start the service --
objService.StartService()
End if
Next
-
Thank you. I will try it and let you know.
-
Hello Grasshopper, now, that I look more closely at your solution - it is using DTS, but DTS will not work if SQL Agent is not up, right? So can this monitor services on another computer?
-
You could alternatively run it on a different server, or through the Windows Task Scheduler.
-
How about using the MOM (Microsoft Operations Manager) ? It will monitor a plethora of things SQL related, notify you and optionally execute commands/scripts to remedy issues you've encountered. We're in the process of implementing MOM 2K5 for about 150 servers (17 of them are SQL - non-clustered and clustered) and it is really awsome.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Thank you, guys, for your suggestions. Hopefully, soemthing will work for me.
-
use xp_servicecontrol.
-
Hi barsuk,
what's "action" is available for this xp_servicecontrol? looks like only "start" and "stop", no "status"... how can I use this xp to check service running or not?
-
Not exactly.
You could run :
EXEC master..xp_servicecontrol 'QueryState', 'SQLServerAgent' and then capture the result in temp table; and based on a response run a job. The only problem you might have that you might want to use another server (hub) for checking the status of your dependent servers. If Agent is down your job won't run.
-
how about using ASP script instead
here is a simple example (with no error checking)
'Code start --------------------
<%
dim whattodo, sql_agent_status
sql_agent_status = "UNKNOWN"
whattodo = request.form("scriptaction")
if whattodo = "check" then
runscripta request.form("db_server"),request.form("db_pw"),"EXEC master..xp_servicecontrol 'QueryState', 'SQLServerAgent'"
elseif whattodo = "start" then
runscriptb request.form("db_server"),request.form("db_pw"),"EXEC master..xp_servicecontrol 'Start', 'SQLServerAgent'"
runscripta request.form("db_server"),request.form("db_pw"),"EXEC master..xp_servicecontrol 'QueryState', 'SQLServerAgent'"
elseif whattodo = "stop" then
runscriptb request.form("db_server"),request.form("db_pw"),"EXEC master..xp_servicecontrol 'Stop', 'SQLServerAgent'"
runscripta request.form("db_server"),request.form("db_pw"),"EXEC master..xp_servicecontrol 'QueryState', 'SQLServerAgent'"
end if
sub runscripta(sServer,sPW,sScript)
dim ConnStr,SQL_Conn,RS1
ConnStr = "Driver={SQL Server}; Server=" & sServer & "; Database=master; UID=sa; PWD=" & sPW
Set SQL_Conn = Server.CreateObject ("ADODB.Connection")
SQL_Conn.Open ConnStr
Set RS1 = Server.CreateObject ("ADODB.Recordset")
Set RS1 = SQL_Conn.execute(sScript)
if not RS1.eof then
sql_agent_status = RS1.fields("Current Service State")
end if
RS1.close
SQL_Conn.close
end sub
sub runscriptb(sServer,sPW,sScript)
dim ConnStr,ADOcommand
ConnStr = "Driver={SQL Server}; Server=" & sServer & "; Database=master; UID=sa; PWD=" & sPW
set ADOcommand = CreateObject("ADODB.Command")
ADOcommand.ActiveConnection = ConnStr
ADOcommand.CommandType = 1
ADOcommand.CommandText = sScript
ADOcommand.Execute
end sub
%>
<html><head><title>sqlagent</title></head>
<script language="JavaScript">function runscript(sScript){document.sql_a.scriptaction.value = sScript;document.sql_a.submit();}</script>
<body><form name="sql_a" action="sqlagent.asp" method="post">
<table border="0" cellpadding="2" width="80%">
<tr>
<td width="100%" colspan="2" align="center"><u><font face="Verdana">SQL Agent Checker</font></u><p> </td>
</tr>
<tr>
<td width="50%"><font face="Verdana">Database Server</font></td><td width="50%"><input type="text" name="db_server" size="20" value="<% = request.form("db_server") %>" style="font-family: Verdana"></td>
</tr>
<tr>
<td width="50%"><font face="Verdana">SA Password</font></td><td width="50%"><input type="password" name="db_pw" size="20" style="font-family: Verdana" value="<% = request.form("db_pw") %>"></td>
</tr>
<tr>
<td width="50%"><font face="Verdana"><a href="javascript:runscript('check');">SQL Agent Status</a></font></td><td width="50%"><input name="db_a_status" size="20" style="font-family: Verdana; text-transform: capitalize" value="<% = sql_agent_status %>"><input type="hidden" name="scriptaction" size="20" style="font-family: Verdana; "></td>
</tr>
<tr>
<td width="100%" colspan="2" align="center"> <p><font face="Verdana"><a href="javascript:runscript('start');">Start SQL Agent</a> <a href="javascript:runscript('stop');">Stop SQL Agent</a></font></p><p> </td>
</tr>
</table></form></body></html>
'Code end --------------------
Steve
-
that's awesome, thank you very much. barsuk.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply