December 20, 2011 at 7:42 am
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.
December 29, 2011 at 4:29 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply