CREATE procedure Alerta_Servidor_Cluster_Down AS -- Executar uma vez por minuto declare @subject varchar(500),@Fl_Tipo tinyint DECLARE @Reportdate DATETIME DECLARE @AlertaLogHeader VARCHAR(MAX) DECLARE @AlertaLogTable VARCHAR(MAX) DECLARE @emptybody2 VARCHAR(MAX) DECLARE @importance as varchar(6) DECLARE @EmailBody VARCHAR(MAX) if exists ( select * from Sys.dm_os_cluster_nodes where status_description <> 'up' ) begin select @Fl_Tipo = Fl_Tipo from Alerta where Id_Alerta = (select MAX(Id_Alerta) from Alerta where Nm_Alerta = 'Alerta_Servidor_Cluster_Down' ) if isnull(@Fl_Tipo,0) = 0 -- só manda alerta 1 vez begin SET @Reportdate =CONVERT(VARCHAR(10),GETDATE(),121) SET @AlertaLogHeader='' SET @AlertaLogHeader=@AlertaLogHeader+'
Status servidores do Cluster
' SET @AlertaLogHeader=@AlertaLogHeader+'
' SET @AlertaLogTable= cast( ( SELECT td = NodeName + ''+ Status_Description+ ''+ cast(is_current_owner as varchar)+ + '' FROM ( select NodeName,Status_Description,is_current_owner from Sys.dm_os_cluster_nodes ) as d order by NodeName FOR XML PATH( 'tr' ), type ) AS VARCHAR(MAX) ) SET @AlertaLogTable = REPLACE( replace( replace( @AlertaLogTable, '<', '<' ), '>', '>' ) , '', '') SET @AlertaLogTable= '' + '' + replace( replace( @AlertaLogTable, '<', '<' ), '>', '>' ) + '
Servidor Status Servidor Ativo
' SET @emptybody2='' SET @emptybody2 = '' + '' + REPLACE( REPLACE( isnull(@emptybody2,''), '<', '<' ), '>', '>' ) + '
' /************************************************************** Sending Email ***************************************************************/ SET @importance ='High' SET @subject = 'ALERTA: Existe um servidor do cluster que não está UP na instância: ' + @@servername SELECT @EmailBody = @AlertaLogHeader + @emptybody2 + @AlertaLogTable+@emptybody2 EXEC msdb.dbo.sp_send_dbmail @profile_name = 'seu profile', @recipients = 'seu e-mail', @subject = @subject , @body = @EmailBody , @body_format = 'HTML' , @importance=@importance insert into Alerta (Nm_Alerta,Ds_Mensagem,Fl_Tipo) select 'Alerta_Servidor_Cluster_Down',@subject, 1 end end else begin select @Fl_Tipo = Fl_Tipo from Alerta where Id_Alerta = (select MAX(Id_Alerta) from Alerta where Nm_Alerta = 'Alerta_Servidor_Cluster_Down' ) if @Fl_Tipo = 1 begin SET @Reportdate =CONVERT(VARCHAR(10),GETDATE(),121) SET @AlertaLogHeader='' SET @AlertaLogHeader=@AlertaLogHeader+'
Status servidores do Cluster
' SET @AlertaLogHeader=@AlertaLogHeader+'
' SET @AlertaLogTable= cast( ( SELECT td = NodeName + ''+ Status_Description+ ''+ cast(is_current_owner as varchar)+ + '' FROM ( select NodeName,Status_Description,is_current_owner from Sys.dm_os_cluster_nodes ) as d order by NodeName FOR XML PATH( 'tr' ), type ) AS VARCHAR(MAX) ) SET @AlertaLogTable = REPLACE( replace( replace( @AlertaLogTable, '<', '<' ), '>', '>' ) , '', '') SET @AlertaLogTable= '' + '' + replace( replace( @AlertaLogTable, '<', '<' ), '>', '>' ) + '
Servidor Status Servidor Ativo
' SET @emptybody2='' SET @emptybody2 = '' + '' + REPLACE( REPLACE( isnull(@emptybody2,''), '<', '<' ), '>', '>' ) + '
' SET @importance ='High' SET @subject = 'CLEAR: Todos os servidores do cluster estão UP na instância:' + @@servername DECLARE @recipientsList varchar(8000) SELECT @EmailBody = @AlertaLogHeader + @emptybody2 + @AlertaLogTable+@emptybody2 -- espaço em disco EXEC msdb.dbo.sp_send_dbmail @profile_name = 'seu profile', @recipients = 'seu e-mail', @subject = @subject , @body = @EmailBody , @body_format = 'HTML' , @importance=@importance insert into Alerta (Nm_Alerta,Ds_Mensagem,Fl_Tipo) select 'Alerta_Status_Membros_Cluster',@subject, 0 end end