SQL Server Agent Notification
-
Is there a way to setup email notification when sql server agent is stopped or not running. How to detect this. I tried setting up recovery notification on server services but it only works when the service is failed. Any idea how to resolve this?
-
The first thing that comes to mind (after recovery options) is maybe a scheduled script that checks the service state, send an e-mail if stopped. something like:
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colListOfServices = objWMIService.ExecQuery _
("Select * from Win32_Service where name = 'SQLSERVERAGENT'")
For Each objService in colListOfServices
IF (objService.State) = "Stopped" then
OMG Panic! send e-mails! page operators!
else
oh well, carry on then
end if
Next
Then I assume you'd also want to know if the service reads as started but isn't responding; There *may* be a way to do it in Performance logs an alerts in computer management with the SQL counters, but I'm not sure myself. I'll have to do some reading on it.
What also comes to mind is having a c# script that tries to interface with the agent, and if it fails it sends an alert;
; fuel for research -
Really you need some third party system or a script as mentioned above. You should have somehting like this to check for other services, not starting, etc.
http://www.serversalive.com/salive/index.asp is reasonable. What's Up, BMC, HP, etc. have more extensive/expensive products.
-
How do i setup this vb script. Do i need to create a small vb app? pls. guide
-
Sorry for not responding sooner Shah,
you can create the vbscript in a normal text editor and give it the extension '.vbs'. Windows recognizes this extension and you can execute it in much the same way you would a batch file. You can get some idea of the syntax and some ideas from microsoft's script center: http://www.microsoft.com/technet/scriptcenter/default.mspx
hope that helps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply