Monday, December 15, 2008

Monitor Status of Service from SQL Server like IIS, SMTP, MS-DTC, SQL Server Agent, Full Text Search etc

I got one assignment today to prepare SQL Script to monitor the SMTP Relay Server status through which we are relaying all our SQL Server monitoring alerts.
First thing came to my mind is to use VBScript or VB Program to do this using WMI but the scope is to use T-SQL Scripts. May If SQL 2008 we can use Powershell, but to finish this task I developed below script.

SET NOCOUNT ON
CREATE TABLE #SMTPChk (SMTPStatus NVARCHAR(255))
INSERT INTO #SMTPChk
exec xp_cmdshell 'Wmic service where (name=''SMTPSVC'') get state'
SELECT 1 FROM #SMTPChk where SMTPStatus like 'Running%'
IF @@ROWCOUNT = 0
Exec master.dbo.xp_smtp_sendmail @from= @@servername, @from_name= @@SERVERNAME, @to= 'mail@mail.com',
@priority = 'HIGH', @message ='Please check the status of SMTP Service in DCADB14',@subject='SMTP Service is Stopped in DCADB14!',
@type= 'text/plain', @server = 'mailserver.company.com'
DROP TABLE #SMTPChk


Here you can replace SMTPSVC with whichever service name you need to monitor and create a job. Planning to learn Powershell for this kind of activities...

No comments:

Post a Comment

Please do not spam!