Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Script For Monitoring State of other servers and alerting !!! Expand / Collapse
Posted Tuesday, December 20, 2011 7:42 AM


Group: General Forum Members
Last Login: Friday, April 10, 2015 3:36 AM
Points: 12, Visits: 281
Please can you help I need a script that will use a centralized server to monitor other sql servers state and if a server is offline it will send a email to alert the dba about which server has a database that is offline . Pleaase do great assist and all your help is greatly appreciated.
Post #1224435
Posted Thursday, December 29, 2011 4:29 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 1, 2016 1:33 PM
Points: 301, Visits: 2,269
I'm sure there is a better way. This was just fast to throw together.

This assumes you have dbmail setup and a valid operator.

Run this on the server you want to monitor:

create database dbadmin
use dbadmin
create table serverstatus (
isawake char(1))
insert into serverstatus

create a least privelidged login and a link server connection from your monitor to the monitoree with that account.

Then throw this in a job step

Declare @isawake char(1)

set @isawake = (select isawake
from [SERVERIWANTTOMONITOR].dbadmin.dbo.serverstatus)

if @isawake <> '1'

raiserror('Test Notify Server Failure', 16, 1, 1)

Set the notifications on the job to email the operator on failure. Also have a second server that points back to the monitor server to validate that is awake as well.

Post #1228117
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse