October 9, 2006 at 12:32 pm
Hi,
I would like to know how to run a script (its checking the services as to whether its started or not) on daily basis.
If sql server agent service is stopped - i cant have this script as an agent job. So thereby how can I run the script as a job independent of sql agent.
Thanks.
October 9, 2006 at 2:19 pm
set your self up a monitoring instance of SQL, use this to run the monitoring.
October 9, 2006 at 3:19 pm
Talk to your sysadmins...they should be able to set up a 'cron' job (or something similar) to do what you are asking.
-SQLBill
October 9, 2006 at 8:27 pm
You could setup a vbscript to check if the service is running....
Something like this (for Apache Tomcat):
strComputer = "."
Set objWMIService = GetObject("winmgmts:" & _
"{impersonationLevel=Impersonate}!\\" & strComputer & "\root\cimv2")
Set colServices = objWMIService.ExecQuery("SELECT * FROM Win32_Service WHERE DisplayName = ""Apache Tomcat""")
Set objWmiEventSource = objWMIService.ExecNotificationQuery _
("SELECT * FROM __InstanceModificationEvent WITHIN 1 WHERE TargetInstance ISA 'Win32_Service' and TargetInstance.DisplayName = 'Apache Tomcat'")
For Each objService in colServices
if (objService.State <> "Running") then
ans = msgbox("Do you want to start '" & objService.DisplayName & "'",4)
if (ans = 6) then 'Yes=6
objService.StartService()
end if
else
ans = msgbox("Do you want to stop '" & objService.DisplayName & "'",4)
if (ans = 6) then 'Yes=6
objservice.StopService()
end if
end if
Next
if (ans = 6) then 'A change has been requested, let's monitor...
Do
Set objService = objWmiEventSource.NextEvent
If objService.TargetInstance.State <> objService.PreviousInstance.State Then
Wscript.Echo "'" & objService.TargetInstance.DisplayName & "'" & _
" is " & objService.TargetInstance.State & _
". The service previously was " & _
objService.PreviousInstance.State & "."
exit do
End If
Loop
end if
October 9, 2006 at 10:43 pm
Setup an AT schedule or use windows scheduler to schedule a job external to sql server that checks for presence of sql server service process.
October 10, 2006 at 2:00 am
Wrote this recently for stopping/starting a service on a remote computer. You can easily adapt this to:
- check if the SQL Server service is running
- if not, start it
Hope this helps.
' stops or starts a service on a remote computer;
Option Explicit
Dim colArgs
Dim strOperation
Dim strServiceName
Dim strComputerName
Dim objWMI
Dim objService
Dim lngRet
Dim strState
Dim intI
Dim strLogfilename
Dim objFso
Dim objLogfile
On Error Resume Next
' get three command line arguments
' 1. start/stop keyword
' 2. service-name (this must be the name in the registry and not the display name)
' 3. computer-name
' 4. [optional fully qualified log filename]
Set colArgs = WScript.Arguments
If colArgs.Count<3 Then
WScript.Echo "Wrong number of arguments"
WScript.Echo "Expecting:"
WScript.Echo "cscript RemoteServiceManager.vbs start service-name computer-name [logfilename]"
WScript.Echo "or"
WScript.Echo "cscript RemoteServiceManager.vbs stop service-name computer-name [logfilename]"
WScript.Quit 1
End If
strOperation = LCase(WScript.Arguments(0))
strServiceName = WScript.Arguments(1)
strComputerName = WScript.Arguments(2)
' open log file if requested
strLogfilename=""
If colArgs.Count > 3 Then
strLogfilename = WScript.Arguments(3)
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objLogfile = objFso.OpenTextFile(strLogfilename, 8, True) ' open for append(8) and create it if doesn't exist(True)
objLogfile.WriteLine Ts() & " " & strOperation & " " & strServiceName & " " & strComputerName
End If
' connect to remote computer
Set objWMI = GetObject("winmgmts:\\" & strComputerName)
If Err.Number <> 0 Then
WScript.Echo CStr(Err.Number) & " " & Err.Description
WScript.Echo "Not authorized to access computer " & strComputerName
If Len(strLogfilename) > 0 Then
objLogfile.WriteLine Ts() & " " & CStr(Err.Number) & " " & Err.Description
objLogfile.WriteLine Ts() & " " & "Not authorized to access computer " & strComputerName
End If
objLogfile.Close
WScript.Quit 1
End If
If strOperation = "stop" Then
Set objService = objWMI.Get("Win32_Service.Name='" & strServiceName & "'")
lngRet = objService.StopService
For intI = 1 To 120 ' loop for 2 minutes until we get a 'stopped' state
Set objService = objWMI.Get("Win32_Service.Name='" & strServiceName & "'")
strState = objService.State
WScript.Echo "State is " & strState
If LCase(strState) = "stopped" Then Exit For
WScript.Sleep 500 ' half a second
Next
If LCase(strState) <> "stopped" Then
WScript.Echo "Unable to stop service " & strServiceName & " (" & strState & ")"
If Len(strLogfilename) > 0 Then
objLogfile.WriteLine Ts() & " " & "Unable to stop service " & strServiceName & " (" & strState & ")"
End If
objLogfile.Close
WScript.Quit 1
End If
End If
If strOperation = "start" Then
Set objService = objWMI.Get("Win32_Service.Name='" & strServiceName & "'")
lngRet = objService.StartService
For intI = 1 To 120 ' loop for 2 minutes until we get a 'running' state
Set objService = objWMI.Get("Win32_Service.Name='" & strServiceName & "'")
strState = objService.State
WScript.Echo "State is " & strState
If LCase(strState) = "running" Then Exit For
WScript.Sleep 500 ' half a second
Next
If LCase(strState) <> "running" Then
WScript.Echo "Unable to start service " & strServiceName & " (" & strState & ")"
If Len(strLogfilename) > 0 Then
objLogfile.WriteLine Ts() & " " & "Unable to start service " & strServiceName & " (" & strState & ")"
End If
objLogfile.Close
WScript.Quit 1
End If
End If
If strOperation <> "start" And strOperation <> "stop" Then
WScript.Echo "Invalid operation"
If Len(strLogfilename) > 0 Then
objLogfile.WriteLine Ts() & " " & "Invalid operation"
End If
objLogfile.Close
WScript.Quit 1
End If
If Err.Number <> 0 Then
WScript.Echo CStr(Err.Number) & " " & Err.Description
If Len(strLogfilename) > 0 Then
objLogfile.WriteLine Ts() & " " & CStr(Err.Number) & " " & Err.Description
objLogfile.Close
WScript.Quit 1
End If
End If
If Len(strLogfilename) > 0 Then
objLogfile.WriteLine Ts() & " " & "OK"
objLogfile.Close
End If
WScript.Quit 0
Function Ts()
Ts = CStr(Year(Now)) & "-" & _
Mid(CStr(Month(Now)+100), 2) & "-" & _
Mid(CStr(Day(Now)+100), 2) & " " & _
Mid(CStr(Hour(Now)+100), 2) & ":" & _
Mid(CStr(Minute(Now)+100), 2) & ":" & _
Mid(CStr(Second(Now)+100), 2)
End Function
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply