October 7, 2011 at 1:40 am
Hi guys,
I'm trying to create an alert to fire whenever a sql instance gets moved to another node. So what I've noticed is that whenever this happens in the error log you get a text like:
The NETBIOS name of the local node that is running the server is 'CLUSTER_NODE-NAME'. This is an informational message only. No user action is required.
Anyways so what I was thinking was to create an alert that will read the log for this message like this:
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'INSTANCE_Failover_Alert',
@message_id=0,
@severity=10,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@event_description_keyword=N'The NETBIOS name',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'INSTANCE_Failover_Alert', @operator_name=N'GTS-DBAs', @notification_method = 1
GO
I made sure that I get the severity number right and everything was built but the alert did not fire, here's the error table from MSDN
http://msdn.microsoft.com/en-us/library/cc645895.aspx
I even put the error number and the same thing, not firing.
Any ideas how I can make this happen, without creating additional stored procedures and tables, just this kind of alert.
Note that I've previously used this type of alerts for Long I/O requests and it worked quite well. The DB Mail is set-up and it works, the operator for this alert has been used and works
Thanks in advance for your help
October 7, 2011 at 7:44 am
I suppose the notification doesn't fire because the instance is not running during the failover.
-- Gianluca Sartori
October 7, 2011 at 8:19 am
Yea, maybe you're right, the instance is in a start-up mode, but that text is in the actual sql log.
Anyway, I managed to find a way around that using the following command:
xp_ReadErrorLog 0,1,'NETBIOS'
that's gonna search the current sql log for NETBIOS entry and return, if exists, details about it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply