-- Escolha a base de dados para criar o Script USE BancoDados /* Instrução de utilização: -- Alterar para o profile do seu servidor: @profile_name = 'MSSQLServer', --Alterar para o e-mail que você quer enviar o alerta @recipients = 'seue-mail@seudominio.com', */ GO /******************************************************************************************************************************* -- Tabela de controle que será utilizada para armazenar o Historico dos Alertas. *******************************************************************************************************************************/ CREATE TABLE [dbo].[Alerta] ( [Id_Alerta] INT IDENTITY PRIMARY KEY, [Nm_Alerta] VARCHAR(200), [Ds_Mensagem] VARCHAR(2000), [Fl_Tipo] TINYINT, -- 0: CLEAR / 1: ALERTA [Dt_Alerta] DATETIME DEFAULT(GETDATE()) ) GO /******************************************************************************************************************************* -- ALERTA: PROCESSO BLOQUEADO *******************************************************************************************************************************/ CREATE PROCEDURE [dbo].[stpAlerta_Processo_Bloqueado] AS BEGIN SET NOCOUNT ON -- Declara as variaveis DECLARE @Subject VARCHAR(500), @Fl_Tipo TINYINT, @Qtd_Segundos INT, @Consulta VARCHAR(8000), @Importance AS VARCHAR(6), @Dt_Atual DATETIME, @EmailBody VARCHAR(MAX), @AlertaLockHeader VARCHAR(MAX), @AlertaLockTable VARCHAR(MAX), @EmptyBodyEmail VARCHAR(MAX), @AlertaLockRaizHeader VARCHAR(MAX), @AlertaLockRaizTable VARCHAR(MAX), @Qt_Tempo_Lock INT, @Qt_Tempo_Raiz_Lock INT -- Quantidade em Minutos SELECT @Qt_Tempo_Lock = 2, -- Query que esta sendo bloqueada (rodando a mais de 2 minutos) @Qt_Tempo_Raiz_Lock = 1 -- Query que esta gerando o lock (rodando a mais de 1 minuto) -------------------------------------------------------------------------------------------------------------------------------- -- Cria Tabela para armazenar os Dados da SP_WHOISACTIVE -------------------------------------------------------------------------------------------------------------------------------- -- Cria a tabela que ira armazenar os dados dos processos IF ( OBJECT_ID('tempdb..#Resultado_WhoisActive') IS NOT NULL ) DROP TABLE #Resultado_WhoisActive CREATE TABLE #Resultado_WhoisActive ( [dd hh:mm:ss.mss] VARCHAR(20), [database_name] NVARCHAR(128), [login_name] NVARCHAR(128), [host_name] NVARCHAR(128), [start_time] DATETIME, [status] VARCHAR(30), [session_id] INT, [blocking_session_id] INT, [wait_info] VARCHAR(MAX), [open_tran_count] INT, [CPU] VARCHAR(MAX), [reads] VARCHAR(MAX), [writes] VARCHAR(MAX), [sql_command] XML ) -- Seta a hora atual SELECT @Dt_Atual = GETDATE() -------------------------------------------------------------------------------------------------------------------------------- -- Carrega os Dados da SP_WHOISACTIVE -------------------------------------------------------------------------------------------------------------------------------- -- Retorna todos os processos que estão sendo executados no momento EXEC [dbo].[sp_WhoIsActive] @get_outer_command = 1, @output_column_list = '[dd hh:mm:ss.mss][database_name][login_name][host_name][start_time][status][session_id][blocking_session_id][wait_info][open_tran_count][CPU][reads][writes][sql_command]', @destination_table = '#Resultado_WhoisActive' -- Altera a coluna que possui o comando SQL ALTER TABLE #Resultado_WhoisActive ALTER COLUMN [sql_command] VARCHAR(MAX) UPDATE #Resultado_WhoisActive SET [sql_command] = REPLACE( REPLACE( REPLACE( REPLACE( CAST([sql_command] AS VARCHAR(1000)), '', ''), '>', '>'), '<', '') -- select * from #Resultado_WhoisActive -- Verifica se não existe nenhum processo em Execução IF NOT EXISTS ( SELECT TOP 1 * FROM #Resultado_WhoisActive ) BEGIN INSERT INTO #Resultado_WhoisActive SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL END -- Verifica o último Tipo do Alerta registrado -> 0: CLEAR / 1: ALERTA SELECT @Fl_Tipo = [Fl_Tipo] FROM [dbo].[Alerta] WHERE [Id_Alerta] = (SELECT MAX(Id_Alerta) FROM [dbo].[Alerta] WHERE [Nm_Alerta] = 'Processo Bloqueado' ) /******************************************************************************************************************************* -- Verifica se existe algum Processo Bloqueado *******************************************************************************************************************************/ IF EXISTS ( SELECT NULL FROM #Resultado_WhoisActive A JOIN #Resultado_WhoisActive B ON A.[blocking_session_id] = B.[session_id] WHERE DATEDIFF(SECOND,A.[start_time], @Dt_Atual) > @Qt_Tempo_Lock * 60 -- A query que está sendo bloqueada está rodando a mais 2 minutos AND DATEDIFF(SECOND,B.[start_time], @Dt_Atual) > @Qt_Tempo_Raiz_Lock * 60 -- A query que está bloqueando está rodando a mais de 1 minuto ) BEGIN -- INICIO - ALERTA IF ISNULL(@Fl_Tipo, 0) = 0 -- Envia o Alerta apenas uma vez BEGIN -------------------------------------------------------------------------------------------------------------------------------- -- Verifica a quantidade de processos bloqueados -------------------------------------------------------------------------------------------------------------------------------- -- Declara a variavel e retorna a quantidade de Queries Lentas DECLARE @QtdProcessosBloqueados INT = ( SELECT COUNT(*) FROM #Resultado_WhoisActive A JOIN #Resultado_WhoisActive B ON A.[blocking_session_id] = B.[session_id] WHERE DATEDIFF(SECOND,A.[start_time], @Dt_Atual) > @Qt_Tempo_Lock * 60 AND DATEDIFF(SECOND,B.[start_time], @Dt_Atual) > @Qt_Tempo_Raiz_Lock * 60 ) DECLARE @QtdProcessosBloqueadosLocks INT = ( SELECT COUNT(*) FROM #Resultado_WhoisActive A WHERE [blocking_session_id] IS NOT NULL ) -------------------------------------------------------------------------------------------------------------------------------- -- Verifica o Nivel dos Locks -------------------------------------------------------------------------------------------------------------------------------- ALTER TABLE #Resultado_WhoisActive ADD Nr_Nivel_Lock TINYINT -- Nivel 0 UPDATE A SET Nr_Nivel_Lock = 0 FROM #Resultado_WhoisActive A WHERE blocking_session_id IS NULL AND session_id IN ( SELECT DISTINCT blocking_session_id FROM #Resultado_WhoisActive WHERE blocking_session_id IS NOT NULL) UPDATE A SET Nr_Nivel_Lock = 1 FROM #Resultado_WhoisActive A WHERE Nr_Nivel_Lock IS NULL AND blocking_session_id IN ( SELECT DISTINCT session_id FROM #Resultado_WhoisActive WHERE Nr_Nivel_Lock = 0) UPDATE A SET Nr_Nivel_Lock = 2 FROM #Resultado_WhoisActive A WHERE Nr_Nivel_Lock IS NULL AND blocking_session_id IN ( SELECT DISTINCT session_id FROM #Resultado_WhoisActive WHERE Nr_Nivel_Lock = 1) UPDATE A SET Nr_Nivel_Lock = 3 FROM #Resultado_WhoisActive A WHERE Nr_Nivel_Lock IS NULL AND blocking_session_id IN ( SELECT DISTINCT session_id FROM #Resultado_WhoisActive WHERE Nr_Nivel_Lock = 2) -- Tratamento quando não tem um Lock Raiz IF NOT EXISTS(select * from #Resultado_WhoisActive where Nr_Nivel_Lock IS NOT NULL) BEGIN UPDATE A SET Nr_Nivel_Lock = 0 FROM #Resultado_WhoisActive A WHERE session_id IN ( SELECT DISTINCT blocking_session_id FROM #Resultado_WhoisActive WHERE blocking_session_id IS NOT NULL) UPDATE A SET Nr_Nivel_Lock = 1 FROM #Resultado_WhoisActive A WHERE Nr_Nivel_Lock IS NULL AND blocking_session_id IN ( SELECT DISTINCT session_id FROM #Resultado_WhoisActive WHERE Nr_Nivel_Lock = 0) UPDATE A SET Nr_Nivel_Lock = 2 FROM #Resultado_WhoisActive A WHERE Nr_Nivel_Lock IS NULL AND blocking_session_id IN ( SELECT DISTINCT session_id FROM #Resultado_WhoisActive WHERE Nr_Nivel_Lock = 1) UPDATE A SET Nr_Nivel_Lock = 3 FROM #Resultado_WhoisActive A WHERE Nr_Nivel_Lock IS NULL AND blocking_session_id IN ( SELECT DISTINCT session_id FROM #Resultado_WhoisActive WHERE Nr_Nivel_Lock = 2) END /******************************************************************************************************************************* -- CRIA O EMAIL - ALERTA *******************************************************************************************************************************/ -------------------------------------------------------------------------------------------------------------------------------- -- ALERTA - HEADER - RAIZ LOCK -------------------------------------------------------------------------------------------------------------------------------- SET @AlertaLockRaizHeader = '' SET @AlertaLockRaizHeader = @AlertaLockRaizHeader + '
TOP 50 - Processos Raiz Lock
' SET @AlertaLockRaizHeader = @AlertaLockRaizHeader + '
' -------------------------------------------------------------------------------------------------------------------------------- -- ALERTA - BODY - RAIZ LOCK -------------------------------------------------------------------------------------------------------------------------------- SET @AlertaLockRaizTable = CAST( ( SELECT td = [Nr_Nivel_Lock] + '' + '' + [Duração] + '' + '' + [database_name] + '' + '' + [login_name] + '' + '' + [host_name] + '' + '' + [start_time] + '' + '' + [status] + '' + '' + [session_id] + '' + '' + [blocking_session_id] + '' + '' + [Wait] + '' + '' + [open_tran_count] + '' + '' + [CPU] + '' + '' + [reads] + '' + '' + [writes] + '' + '' + [sql_command] + '' FROM ( -- Dados da Tabela do EMAIL SELECT TOP 50 CAST(Nr_Nivel_Lock AS VARCHAR) AS [Nr_Nivel_Lock], ISNULL([dd hh:mm:ss.mss], '-') AS [Duração], ISNULL([database_name], '-') AS [database_name], ISNULL([login_name], '-') AS [login_name], ISNULL([host_name], '-') AS [host_name], ISNULL(CONVERT(VARCHAR(20), [start_time], 120), '-') AS [start_time], ISNULL([status], '-') AS [status], ISNULL(CAST([session_id] AS VARCHAR), '-') AS [session_id], ISNULL(CAST([blocking_session_id] AS VARCHAR), '-') AS [blocking_session_id], ISNULL([wait_info], '-') AS [Wait], ISNULL(CAST([open_tran_count] AS VARCHAR), '-') AS [open_tran_count], ISNULL([CPU], '-') AS [CPU], ISNULL([reads], '-') AS [reads], ISNULL([writes], '-') AS [writes], ISNULL(SUBSTRING([sql_command], 1, 300), '-') AS [sql_command] FROM #Resultado_WhoisActive WHERE Nr_Nivel_Lock IS NOT NULL ORDER BY [Nr_Nivel_Lock], [start_time] ) AS D ORDER BY [Nr_Nivel_Lock], [start_time] FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX) ) -- Corrige a Formatação da Tabela SET @AlertaLockRaizTable = REPLACE( REPLACE( REPLACE( @AlertaLockRaizTable, '<', '<'), '>', '>'), '', '') -- Títulos da Tabela do EMAIL SET @AlertaLockRaizTable = '' + '' + REPLACE( REPLACE( @AlertaLockRaizTable, '<', '<'), '>', '>') + '
Nivel Lock [dd hh:mm:ss.mss] Database Login Host Name Hora Início Status ID Sessão ID Sessão Bloqueando Wait Transações Abertas CPU Reads Writes Query
' -------------------------------------------------------------------------------------------------------------------------------- -- ALERTA - HEADER -------------------------------------------------------------------------------------------------------------------------------- SET @AlertaLockHeader = '' SET @AlertaLockHeader = @AlertaLockHeader + '
TOP 50 - Processos executando no Banco de Dados
' SET @AlertaLockHeader = @AlertaLockHeader + '
' -------------------------------------------------------------------------------------------------------------------------------- -- ALERTA - BODY -------------------------------------------------------------------------------------------------------------------------------- SET @AlertaLockTable = CAST( ( SELECT td = [Duração] + '' + '' + [database_name] + '' + '' + [login_name] + '' + '' + [host_name] + '' + '' + [start_time] + '' + '' + [status] + '' + '' + [session_id] + '' + '' + [blocking_session_id] + '' + '' + [Wait] + '' + '' + [open_tran_count] + '' + '' + [CPU] + '' + '' + [reads] + '' + '' + [writes] + '' + '' + [sql_command] + '' FROM ( -- Dados da Tabela do EMAIL SELECT TOP 50 ISNULL([dd hh:mm:ss.mss], '-') AS [Duração], ISNULL([database_name], '-') AS [database_name], ISNULL([login_name], '-') AS [login_name], ISNULL([host_name], '-') AS [host_name], ISNULL(CONVERT(VARCHAR(20), [start_time], 120), '-') AS [start_time], ISNULL([status], '-') AS [status], ISNULL(CAST([session_id] AS VARCHAR), '-') AS [session_id], ISNULL(CAST([blocking_session_id] AS VARCHAR), '-') AS [blocking_session_id], ISNULL([wait_info], '-') AS [Wait], ISNULL(CAST([open_tran_count] AS VARCHAR), '-') AS [open_tran_count], ISNULL([CPU], '-') AS [CPU], ISNULL([reads], '-') AS [reads], ISNULL([writes], '-') AS [writes], ISNULL(SUBSTRING([sql_command], 1, 300), '-') AS [sql_command] FROM #Resultado_WhoisActive ORDER BY [start_time] ) AS D ORDER BY [start_time] FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX) ) -- Corrige a Formatação da Tabela SET @AlertaLockTable = REPLACE( REPLACE( REPLACE( @AlertaLockTable, '<', '<'), '>', '>'), '', '') -- Títulos da Tabela do EMAIL SET @AlertaLockTable = '' + '' + REPLACE( REPLACE( @AlertaLockTable, '<', '<'), '>', '>') + '
[dd hh:mm:ss.mss] Database Login Host Name Hora Início Status ID Sessão ID Sessão Bloqueando Wait Transações Abertas CPU Reads Writes Query
' -------------------------------------------------------------------------------------------------------------------------------- -- Insere um Espaço em Branco no EMAIL -------------------------------------------------------------------------------------------------------------------------------- SET @EmptyBodyEmail = '' SET @EmptyBodyEmail = '' + '' + REPLACE( REPLACE( ISNULL(@EmptyBodyEmail,''), '<', '<'), '>', '>') + '
' /******************************************************************************************************************************* -- Seta as Variáveis do EMAIL *******************************************************************************************************************************/ SELECT @Importance = 'High', @Subject = 'ALERTA: Existe(m) ' + CAST(@QtdProcessosBloqueados AS VARCHAR) + ' Processo(s) Bloqueado(s) a mais de ' + CAST((@Qt_Tempo_Lock) AS VARCHAR) + ' minuto(s)' + ' e um total de ' + CAST(@QtdProcessosBloqueadosLocks AS VARCHAR) + ' Lock(s) no Servidor: ' + @@SERVERNAME, @EmailBody = @AlertaLockRaizHeader + @EmptyBodyEmail + @AlertaLockRaizTable + @EmptyBodyEmail + @AlertaLockHeader + @EmptyBodyEmail + @AlertaLockTable + @EmptyBodyEmail /******************************************************************************************************************************* -- ENVIA O EMAIL - ALERTA *******************************************************************************************************************************/ EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'MSSQLServer', @recipients = 'seue-mail@seudominio.com', @subject = @Subject, @body = @EmailBody, @body_format = 'HTML', @importance = @Importance /******************************************************************************************************************************* -- Insere um Registro na Tabela de Controle dos Alertas -> Fl_Tipo = 1 : ALERTA *******************************************************************************************************************************/ INSERT INTO [dbo].[Alerta] ( [Nm_Alerta], [Ds_Mensagem], [Fl_Tipo] ) SELECT 'Processo Bloqueado', @Subject, 1 END END -- FIM - ALERTA ELSE BEGIN -- INICIO - CLEAR IF @Fl_Tipo = 1 BEGIN /******************************************************************************************************************************* -- CRIA O EMAIL - CLEAR *******************************************************************************************************************************/ -------------------------------------------------------------------------------------------------------------------------------- -- CLEAR - HEADER -------------------------------------------------------------------------------------------------------------------------------- SET @AlertaLockHeader = '' SET @AlertaLockHeader = @AlertaLockHeader + '
Processos executando no Banco de Dados
' SET @AlertaLockHeader = @AlertaLockHeader + '
' -------------------------------------------------------------------------------------------------------------------------------- -- CLEAR - BODY -------------------------------------------------------------------------------------------------------------------------------- SET @AlertaLockTable = CAST( ( SELECT td = [Duração] + '' + '' + [database_name] + '' + '' + [login_name] + '' + '' + [start_time] + '' + '' + [status] + '' + '' + [session_id] + '' + '' + [blocking_session_id] + '' + '' + [Wait] + '' + '' + [open_tran_count] + '' + '' + [CPU] + '' + '' + [reads] + '' + '' + [writes] + '' + '' + [sql_command] + '' FROM ( -- Dados da Tabela do EMAIL SELECT ISNULL([dd hh:mm:ss.mss], '-') AS [Duração], ISNULL([database_name], '-') AS [database_name], ISNULL([login_name], '-') AS [login_name], ISNULL(CONVERT(VARCHAR(20), [start_time], 120), '-') AS [start_time], ISNULL([status], '-') AS [status], ISNULL(CAST([session_id] AS VARCHAR), '-') AS [session_id], ISNULL(CAST([blocking_session_id] AS VARCHAR), '-') AS [blocking_session_id], ISNULL([wait_info], '-') AS [Wait], ISNULL(CAST([open_tran_count] AS VARCHAR), '-') AS [open_tran_count], ISNULL([CPU], '-') AS [CPU], ISNULL([reads], '-') AS [reads], ISNULL([writes], '-') AS [writes], ISNULL(SUBSTRING([sql_command], 1, 300), '-') AS [sql_command] FROM #Resultado_WhoisActive ) AS D ORDER BY [start_time] FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX) ) -- Corrige a Formatação da Tabela SET @AlertaLockTable = REPLACE( REPLACE( REPLACE( @AlertaLockTable, '<', '<'), '>', '>'), '', '') -- Títulos da Tabela do EMAIL SET @AlertaLockTable = '' + '' + REPLACE( REPLACE( @AlertaLockTable, '<', '<'), '>', '>') + '
[dd hh:mm:ss.mss] Database Login Hora Início Status ID Sessão ID Sessão Bloqueando Wait Transações Abertas CPU Reads Writes Query
' -------------------------------------------------------------------------------------------------------------------------------- -- Insere um Espaço em Branco no EMAIL -------------------------------------------------------------------------------------------------------------------------------- SET @EmptyBodyEmail = '' SET @EmptyBodyEmail = '' + '' + REPLACE( REPLACE( ISNULL(@EmptyBodyEmail,''), '<', '<'), '>', '>') + '
' /******************************************************************************************************************************* -- Seta as Variáveis do EMAIL *******************************************************************************************************************************/ SELECT @Importance = 'High', @Subject = 'CLEAR: Não existe mais algum Processo Bloqueado a mais de ' + CAST((@Qt_Tempo_Lock) AS VARCHAR) + ' minuto(s) no Servidor: ' + @@SERVERNAME, @EmailBody = @AlertaLockHeader + @EmptyBodyEmail + @AlertaLockTable + @EmptyBodyEmail /******************************************************************************************************************************* -- ENVIA O EMAIL - CLEAR *******************************************************************************************************************************/ EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'MSSQLServer', @recipients = 'seue-mail@seudominio.com', @subject = @Subject, @body = @EmailBody, @body_format = 'HTML', @importance = @Importance /******************************************************************************************************************************* -- Insere um Registro na Tabela de Controle dos Alertas -> Fl_Tipo = 0 : CLEAR *******************************************************************************************************************************/ INSERT INTO [dbo].[Alerta] ( [Nm_Alerta], [Ds_Mensagem], [Fl_Tipo] ) SELECT 'Processo Bloqueado', @Subject, 0 END END -- FIM - CLEAR END