Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Tuesday, December 20, 2011 7:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 3, 2013 7:13 AM
Points: 12, Visits: 277
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
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 252, Visits: 1,376
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
go
use dbadmin
go
create table serverstatus (
isawake char(1))
GO
insert into serverstatus
values(1)

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'

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


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