/***********************************************************************************************************************************
(C) 2017, Fabricio Lima Soluções em Banco de Dados
Site: http://www.fabriciolima.net/
Feedback: contato@fabriciolima.net
***********************************************************************************************************************************/
/*******************************************************************************************************************************
-- Instruções de utilização do script.
*******************************************************************************************************************************/
/*
Apertar F5 para executar todo o script.
*/
SET NOCOUNT ON
GO
EXEC [sp_configure] 'show advanced option', 1
RECONFIGURE with OVERRIDE
EXEC [sp_configure] 'Ole Automation Procedures', 1
RECONFIGURE with OVERRIDE
EXEC [sp_configure] 'show advanced option', 0
RECONFIGURE with OVERRIDE
GO
USE [Traces]
/*******************************************************************************************************************************
-- Tabela de controle que será utilizada para armazenar o Historico da tabela Suspect Pages.
*******************************************************************************************************************************/
IF ( OBJECT_ID('[dbo].[Historico_Suspect_Pages]') IS NOT NULL )
DROP TABLE [dbo].[Historico_Suspect_Pages]
CREATE TABLE [dbo].[Historico_Suspect_Pages](
[database_id] [int] NOT NULL,
[file_id] [int] NOT NULL,
[page_id] [bigint] NOT NULL,
[event_type] [int] NOT NULL,
[Dt_Corrupcao] [datetime] NOT NULL
) ON [PRIMARY]
/*******************************************************************************************************************************
-- CRIA OS ALERTAS
*******************************************************************************************************************************/
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_Processo_Bloqueado]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_Processo_Bloqueado]
GO
/*******************************************************************************************************************************
-- ALERTA: PROCESSO BLOQUEADO
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_Processo_Bloqueado]
AS
BEGIN
SET NOCOUNT ON
-- Processo Bloqueado
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Processo Bloqueado')
-- 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), @Processo_Bloqueado_Parametro INT, @Qt_Tempo_Raiz_Lock INT,
@EmailDestination VARCHAR(500), @ProfileEmail VARCHAR(200)
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
SELECT @Processo_Bloqueado_Parametro = Vl_Parametro, -- Minutos,
@EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Processo Bloqueado
-- Quantidade em Minutos
SELECT @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 [Id_Alerta_Parametro] = @Id_Alerta_Parametro )
/*******************************************************************************************************************************
-- 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) > @Processo_Bloqueado_Parametro * 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) > @Processo_Bloqueado_Parametro * 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 =
''
+ '
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 |
'
+ REPLACE( REPLACE( @AlertaLockRaizTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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 =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @AlertaLockTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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((@Processo_Bloqueado_Parametro) 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
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @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] + ' | '
+ '' + [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 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
) 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 =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @AlertaLockTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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((@Processo_Bloqueado_Parametro) AS VARCHAR) + ' minuto(s) no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaLockHeader + @EmptyBodyEmail + @AlertaLockTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - CLEAR
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 0
END
END -- FIM - CLEAR
END
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_Arquivo_Log_Full]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_Arquivo_Log_Full]
GO
/*******************************************************************************************************************************
-- ALERTA: ARQUIVO DE LOG FULL
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_Arquivo_Log_Full]
AS
BEGIN
SET NOCOUNT ON
-- Arquivo de Log Full
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Arquivo de Log Full')
-- Declara as variaveis
DECLARE @Tamanho_Minimo_Alerta_log INT, @AlertaLogHeader VARCHAR(MAX), @AlertaLogTable VARCHAR(MAX), @EmptyBodyEmail VARCHAR(MAX),
@Importance AS VARCHAR(6), @EmailBody VARCHAR(MAX), @Subject VARCHAR(500), @Fl_Tipo TINYINT, @Log_Full_Parametro TINYINT,
@ResultadoWhoisactiveHeader VARCHAR(MAX), @ResultadoWhoisactiveTable VARCHAR(MAX), @EmailDestination VARCHAR(500), @ProfileEmail VARCHAR(200)
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
SELECT @Log_Full_Parametro = Vl_Parametro, -- Percentual
@EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Arquivo de Log Full
-- Seta as variaveis
SELECT @Tamanho_Minimo_Alerta_log = 500000 -- 500 MB
-- 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 [Id_Alerta_Parametro] = @Id_Alerta_Parametro )
-- 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
)
-- Cria a tabela que ira armazenar os dados dos arquivos de log
IF ( OBJECT_ID('tempdb..#Alerta_Arquivo_Log_Full') IS NOT NULL )
DROP TABLE #Alerta_Arquivo_Log_Full
SELECT db.[name] AS [DatabaseName] ,
CAST(ls.[cntr_value] / 1024.00 AS DECIMAL(18,2)) AS [cntr_value],
CAST( CAST(lu.[cntr_value] AS FLOAT) /
CASE WHEN CAST(ls.[cntr_value] AS FLOAT) = 0
THEN 1
ELSE CAST(ls.[cntr_value] AS FLOAT)
END AS DECIMAL(18,2)) * 100 AS [Percente_Log_Used]
INTO #Alerta_Arquivo_Log_Full
FROM [sys].[databases] AS db
JOIN [sys].[dm_os_performance_counters] AS lu ON db.[name] = lu.[instance_name]
JOIN [sys].[dm_os_performance_counters] AS ls ON db.[name] = ls.[instance_name]
WHERE lu.[counter_name] LIKE 'Log File(s) Used Size (KB)%'
AND ls.[counter_name] LIKE 'Log File(s) Size (KB)%'
AND ls.[cntr_value] > @Tamanho_Minimo_Alerta_log -- Maior que 100 MB
/*******************************************************************************************************************************
-- Verifica se existe algum LOG com muita utilização
*******************************************************************************************************************************/
IF EXISTS(
SELECT *
FROM #Alerta_Arquivo_Log_Full
WHERE [Percente_Log_Used] > @Log_Full_Parametro
)
BEGIN -- INICIO - ALERTA
IF ISNULL(@Fl_Tipo, 0) = 0 -- Envia o Alerta apenas uma vez
BEGIN
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - DADOS - 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
/*******************************************************************************************************************************
-- ALERTA - CRIA O EMAIL
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER - LOG FULL
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaLogHeader = ''
SET @AlertaLogHeader = @AlertaLogHeader + ' Informações dos Arquivos de Log '
SET @AlertaLogHeader = @AlertaLogHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY - LOG FULL
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaLogTable = CAST( (
SELECT td = [DatabaseName] + ' | '
+ '' + CAST([cntr_value] AS VARCHAR) + ' | '
+ '' + CAST([Percente_Log_Used] AS VARCHAR) + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT [DatabaseName],
[cntr_value],
[Percente_Log_Used]
FROM #Alerta_Arquivo_Log_Full
WHERE [Percente_Log_Used] > @Log_Full_Parametro
) AS D ORDER BY [Percente_Log_Used] DESC
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaLogTable = REPLACE( REPLACE( REPLACE( @AlertaLogTable, '<', '<' ), '>', '>' ), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaLogTable =
''
+ '
Database |
Tamanho Log (MB) |
Percentual Log Utilizado (%) |
'
+ REPLACE( REPLACE( @AlertaLogTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveHeader = ''
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ' Processos executando no Banco de Dados '
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveTable = 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 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
) AS D ORDER BY [start_time]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @ResultadoWhoisactiveTable = REPLACE( REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @ResultadoWhoisactiveTable =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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 algum Arquivo de Log com mais de ' + CAST((@Log_Full_Parametro) AS VARCHAR) + '% de utilização no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaLogHeader + @EmptyBodyEmail + @AlertaLogTable + @EmptyBodyEmail +
@ResultadoWhoisactiveHeader + @EmptyBodyEmail + @ResultadoWhoisactiveTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ALERTA - ENVIA O EMAIL
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END
END -- FIM - ALERTA
ELSE
BEGIN -- INICIO - CLEAR
IF @Fl_Tipo = 1
BEGIN
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - DADOS - 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
/*******************************************************************************************************************************
-- CLEAR - CRIA O EMAIL
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaLogHeader = ''
SET @AlertaLogHeader = @AlertaLogHeader + ' Informações dos Arquivos de Log '
SET @AlertaLogHeader = @AlertaLogHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaLogTable = CAST( (
SELECT td = [DatabaseName] + ' | '
+ '' + CAST([cntr_value] AS VARCHAR) + ' | '
+ '' + CAST([Percente_Log_Used] AS VARCHAR) + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT [DatabaseName],
[cntr_value],
[Percente_Log_Used]
FROM #Alerta_Arquivo_Log_Full
) AS D ORDER BY [Percente_Log_Used] DESC
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaLogTable = REPLACE( REPLACE( REPLACE( @AlertaLogTable, '<', '<' ), '>', '>' ), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaLogTable =
''
+ '
Database |
Tamanho Log (MB) |
Percentual Log Utilizado (%) |
'
+ REPLACE( REPLACE( @AlertaLogTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - HEADER - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveHeader = ''
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ' Processos executando no Banco de Dados '
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - BODY - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveTable = 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 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
) AS D ORDER BY [start_time]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @ResultadoWhoisactiveTable = REPLACE( REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @ResultadoWhoisactiveTable =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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 Arquivo de Log com mais de ' + CAST((@Log_Full_Parametro) AS VARCHAR) + '% de utilização no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaLogHeader + @EmptyBodyEmail + @AlertaLogTable + @EmptyBodyEmail +
@ResultadoWhoisactiveHeader + @EmptyBodyEmail + @ResultadoWhoisactiveTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ALERTA - ENVIA O EMAIL
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 0
END
END -- FIM - CLEAR
END
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_Espaco_Disco]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_Espaco_Disco]
GO
/*******************************************************************************************************************************
-- ALERTA: ESPAÇO DISCO
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_Espaco_Disco]
AS
BEGIN
SET NOCOUNT ON
-- Cria as tabelas que irão armazenar as informações do Espaço em Disco
IF ( OBJECT_ID('tempdb..#dbspace') IS NOT NULL )
DROP TABLE #dbspace
CREATE TABLE #dbspace (
[name] SYSNAME,
[caminho] VARCHAR(200),
[tamanho] VARCHAR(10),
[drive] VARCHAR(30)
)
IF ( OBJECT_ID('tempdb..#espacodisco') IS NOT NULL )
DROP TABLE #espacodisco
CREATE TABLE [#espacodisco] (
[Drive] VARCHAR (10),
[Tamanho (MB)] INT,
[Usado (MB)] INT,
[Livre (MB)] INT,
[Livre (%)] INT,
[Usado (%)] INT,
[Ocupado SQL (MB)] INT,
[Data] SMALLDATETIME
)
IF ( OBJECT_ID('tempdb..#space') IS NOT NULL )
DROP TABLE #space
CREATE TABLE #space (
[drive] CHAR(1),
[mbfree] INT
)
-- Popula as tabelas com as informações sobre o Espaço em Disco
EXEC sp_MSforeachdb 'Use [?] INSERT INTO #dbspace SELECT CONVERT(VARCHAR(25), DB_Name()) ''Database'', CONVERT(VARCHAR(60), FileName), CONVERT(VARCHAR(8), Size / 128) ''Size in MB'', CONVERT(VARCHAR(30), Name) FROM sysfiles'
-- Declara as variaveis
DECLARE @hr INT, @fso INT, @mbtotal INT, @TotalSpace INT, @MBFree INT, @Percentage INT,
@SQLDriveSize INT, @size float, @drive VARCHAR(1), @fso_Method VARCHAR(255)
SELECT @mbtotal = 0,
@mbtotal = 0
EXEC @hr = [master].[dbo].[sp_OACreate] 'Scripting.FilesystemObject', @fso OUTPUT
INSERT INTO #space
EXEC [master].[dbo].[xp_fixeddrives]
-- Utiliza o Cursor para gerar as informações de cada Disco
DECLARE CheckDrives CURSOR FOR SELECT drive,mbfree FROM #space
OPEN CheckDrives
FETCH NEXT FROM CheckDrives INTO @drive, @MBFree
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @fso_Method = 'Drives("' + @drive + ':").TotalSize'
SELECT @SQLDriveSize = SUM(CONVERT(INT, [tamanho]))
FROM #dbspace
WHERE SUBSTRING([caminho], 1, 1) = @drive
EXEC @hr = [sp_OAMethod] @fso, @fso_Method, @size OUTPUT
SET @mbtotal = @size / (1024 * 1024)
INSERT INTO #espacodisco
VALUES( @drive + ':', @mbtotal, @mbtotal - @MBFree, @MBFree, (100 * ROUND(@MBFree, 2) / ROUND(@mbtotal, 2)),
(100 - 100 * ROUND(@MBFree, 2) / ROUND(@mbtotal, 2)), @SQLDriveSize, GETDATE())
FETCH NEXT FROM CheckDrives INTO @drive,@MBFree
END
CLOSE CheckDrives
DEALLOCATE CheckDrives
-- Tabela com os dados resumidos sobre o Espaço em Disco
IF ( OBJECT_ID('_DTS_Espacodisco ') IS NOT NULL )
DROP TABLE _DTS_Espacodisco
SELECT [Drive], [Tamanho (MB)], [Usado (MB)], [Livre (MB)], [Livre (%)], [Usado (%)], ISNULL([Ocupado SQL (MB)], 0) AS [Ocupado SQL (MB)]
INTO [dbo].[_DTS_Espacodisco]
FROM #espacodisco
-- 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
)
-- Declara as variaveis
DECLARE @Subject VARCHAR(500), @Fl_Tipo TINYINT, @Importance AS VARCHAR(6),@EmailBody VARCHAR(MAX),
@AlertaDiscoHeader VARCHAR(MAX),@AlertaDiscoTable VARCHAR(MAX), @EmptyBodyEmail VARCHAR(MAX), @EmailDestination VARCHAR(500),
@ResultadoWhoisactiveHeader VARCHAR(MAX), @ResultadoWhoisactiveTable VARCHAR(MAX), @Espaco_Disco_Parametro INT, @ProfileEmail VARCHAR(200)
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
-- Espaco Disco
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Espaco Disco')
SELECT @Espaco_Disco_Parametro = Vl_Parametro, -- Percentual
@EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Espaco Disco
-- 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 [Id_Alerta_Parametro] = @Id_Alerta_Parametro )
/*******************************************************************************************************************************
-- Verifica o Espaço Livre em Disco
*******************************************************************************************************************************/
IF EXISTS (
SELECT NULL
FROM [dbo].[_DTS_Espacodisco]
WHERE [Usado (%)] > @Espaco_Disco_Parametro
)
BEGIN -- INICIO - ALERTA
IF ISNULL(@Fl_Tipo, 0) = 0 -- Envia o Alerta apenas uma vez
BEGIN
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - DADOS - 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
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaDiscoHeader = ''
SET @AlertaDiscoHeader = @AlertaDiscoHeader + ' Espaço em Disco no Servidor '
SET @AlertaDiscoHeader = @AlertaDiscoHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaDiscoTable = CAST( (
SELECT td = [Drive] + ' | '
+ '' + [Tamanho (MB)] + ' | '
+ '' + [Usado (MB)] + ' | '
+ '' + [Livre (MB)] + ' | '
+ '' + [Livre (%)] + ' | '
+ '' + [Usado (%)] + ' | '
+ '' + [Ocupado SQL (MB)] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT [Drive], CAST([Tamanho (MB)] AS VARCHAR) AS [Tamanho (MB)], CAST([Usado (MB)] AS VARCHAR) AS [Usado (MB)], CAST([Livre (MB)] AS VARCHAR) AS [Livre (MB)],
CAST([Livre (%)] AS VARCHAR) AS [Livre (%)], CAST([Usado (%)] AS VARCHAR) AS [Usado (%)], CAST([Ocupado SQL (MB)] AS VARCHAR) AS [Ocupado SQL (MB)]
FROM [Traces].[dbo].[_DTS_Espacodisco]
) AS D ORDER BY [Drive]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaDiscoTable = REPLACE( REPLACE( REPLACE( @AlertaDiscoTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaDiscoTable =
''
+ '
Drive (%) |
Tamanho (MB) |
Usado (MB) |
Livre (MB) |
Livre (%) |
Usado (%) |
Ocupado SQL (MB) |
'
+ REPLACE( REPLACE( @AlertaDiscoTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveHeader = ''
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ' Processos executando no Banco de Dados '
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveTable = 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 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
) AS D ORDER BY [start_time]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @ResultadoWhoisactiveTable = REPLACE( REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @ResultadoWhoisactiveTable =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- Insere um Espaço em Branco no EMAIL
--------------------------------------------------------------------------------------------------------------------------------
SET @EmptyBodyEmail = ''
SET @EmptyBodyEmail =
'' +
'
|
'
+ REPLACE( REPLACE( ISNULL(@EmptyBodyEmail,''), '<', '<'), '>', '>')
+ ' '
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
SELECT @Importance = 'High',
@Subject = 'ALERTA: Existe algum volume de disco com mais de ' + CAST((@Espaco_Disco_Parametro) AS VARCHAR) + '% de utilização no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaDiscoHeader + @EmptyBodyEmail + @AlertaDiscoTable + @EmptyBodyEmail +
@ResultadoWhoisactiveHeader + @EmptyBodyEmail + @ResultadoWhoisactiveTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END
END -- FIM - ALERTA
ELSE
BEGIN -- INICIO - CLEAR
IF @Fl_Tipo = 1
BEGIN
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - DADOS - 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
/*******************************************************************************************************************************
-- CRIA O EMAIL - CLEAR
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaDiscoHeader = ''
SET @AlertaDiscoHeader = @AlertaDiscoHeader + ' Espaço em Disco no Servidor '
SET @AlertaDiscoHeader = @AlertaDiscoHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaDiscoTable = CAST( (
SELECT td = [Drive] + ' | '
+ '' + [Tamanho (MB)] + ' | '
+ '' + [Usado (MB)] + ' | '
+ '' + [Livre (MB)] + ' | '
+ '' + [Livre (%)] + ' | '
+ '' + [Usado (%)] + ' | '
+ '' + [Ocupado SQL (MB)] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT [Drive], CAST([Tamanho (MB)] AS VARCHAR) AS [Tamanho (MB)], CAST([Usado (MB)] AS VARCHAR) AS [Usado (MB)], CAST([Livre (MB)] AS VARCHAR) AS [Livre (MB)],
CAST([Livre (%)] AS VARCHAR) AS [Livre (%)], CAST([Usado (%)] AS VARCHAR) AS [Usado (%)], CAST([Ocupado SQL (MB)] AS VARCHAR) AS [Ocupado SQL (MB)]
FROM [Traces].[dbo].[_DTS_Espacodisco]
) AS D ORDER BY [Drive]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaDiscoTable = REPLACE( REPLACE( REPLACE( @AlertaDiscoTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaDiscoTable =
''
+ '
Drive (%) |
Tamanho (MB) |
Usado (MB) |
Livre (MB) |
Livre (%) |
Usado (%) |
Ocupado SQL (MB) |
'
+ REPLACE( REPLACE( @AlertaDiscoTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - HEADER - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveHeader = ''
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ' Processos executando no Banco de Dados '
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - BODY - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveTable = 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 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
) AS D ORDER BY [start_time]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @ResultadoWhoisactiveTable = REPLACE( REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @ResultadoWhoisactiveTable =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- Insere um Espaço em Branco no EMAIL
--------------------------------------------------------------------------------------------------------------------------------
SET @EmptyBodyEmail = ''
SET @EmptyBodyEmail =
'' +
'
|
'
+ REPLACE( REPLACE( ISNULL(@EmptyBodyEmail,''), '<', '<'), '>', '>')
+ ' '
/*******************************************************************************************************************************
-- ENVIA O EMAIL - CLEAR
*******************************************************************************************************************************/
SELECT @Importance = 'High',
@Subject = 'CLEAR: Não existe mais algum volume de disco com mais de ' + CAST((@Espaco_Disco_Parametro) AS VARCHAR) + '% de utilização no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaDiscoHeader + @EmptyBodyEmail + @AlertaDiscoTable + @EmptyBodyEmail +
@ResultadoWhoisactiveHeader + @EmptyBodyEmail + @ResultadoWhoisactiveTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 0
END
END -- FIM - CLEAR
END
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_Consumo_CPU]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_Consumo_CPU]
GO
/*******************************************************************************************************************************
-- ALERTA: CONSUMO CPU
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_Consumo_CPU]
AS
BEGIN
SET NOCOUNT ON
-- Consumo CPU
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Consumo CPU')
-- Declara as variaveis
DECLARE @Subject VARCHAR(500), @Fl_Tipo TINYINT, @Importance AS VARCHAR(6), @EmailBody VARCHAR(MAX), @CPU_Parametro INT,
@AlertaCPUAgarradosHeader VARCHAR(MAX), @AlertaCPUAgarradosTable VARCHAR(MAX), @EmptyBodyEmail VARCHAR(MAX),
@ResultadoWhoisactiveHeader VARCHAR(MAX), @ResultadoWhoisactiveTable VARCHAR(MAX), @EmailDestination VARCHAR(500),
@ProfileEmail VARCHAR(200)
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
SELECT @CPU_Parametro = Vl_Parametro, -- Percentual
@EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Consumo CPU
-- 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 [Id_Alerta_Parametro] = @Id_Alerta_Parametro )
--------------------------------------------------------------------------------------------------------------------------------
-- 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
)
--------------------------------------------------------------------------------------------------------------------------------
-- Verifica a utilização da CPU
--------------------------------------------------------------------------------------------------------------------------------
IF ( OBJECT_ID('tempdb..#CPU_Utilization') IS NOT NULL )
DROP TABLE #CPU_Utilization
SELECT TOP(2)
record_id,
[SQLProcessUtilization],
100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization,
[SystemIdle],
100 - SystemIdle AS CPU_Utilization
INTO #CPU_Utilization
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS [record_id],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
[timestamp]
FROM (
SELECT [timestamp], CONVERT(XML, [record]) AS [record]
FROM [sys].[dm_os_ring_buffers]
WHERE [ring_buffer_type] = N'RING_BUFFER_SCHEDULER_MONITOR'
AND [record] LIKE '%%'
) AS X
) AS Y
ORDER BY record_id DESC
/*******************************************************************************************************************************
-- Verifica se o Consumo de CPU está maior do que o parametro
*******************************************************************************************************************************/
IF (
select CPU_Utilization from #CPU_Utilization
where record_id = (select max(record_id) from #CPU_Utilization)
) > @CPU_Parametro
BEGIN -- INICIO - ALERTA
IF (
select CPU_Utilization from #CPU_Utilization
where record_id = (select min(record_id) from #CPU_Utilization)
) > @CPU_Parametro
BEGIN
IF ISNULL(@Fl_Tipo, 0) = 0 -- Envia o Alerta apenas uma vez
BEGIN
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - DADOS - 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
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaCPUAgarradosHeader = ''
SET @AlertaCPUAgarradosHeader = @AlertaCPUAgarradosHeader + ' Consumo de CPU no Servidor '
SET @AlertaCPUAgarradosHeader = @AlertaCPUAgarradosHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaCPUAgarradosTable = CAST( (
SELECT td = [SQLProcessUtilization] + ' | '
+ '' + OtherProcessUtilization + ' | '
+ '' + [SystemIdle] + ' | '
+ '' + CPU_Utilization + ' | '
FROM (
-- Dados da Tabela do EMAIL
select TOP 1
CAST([SQLProcessUtilization] AS VARCHAR) [SQLProcessUtilization],
CAST((100 - SystemIdle - SQLProcessUtilization) AS VARCHAR) as OtherProcessUtilization,
CAST([SystemIdle] AS VARCHAR) AS [SystemIdle],
CAST(100 - SystemIdle AS VARCHAR) AS CPU_Utilization
from #CPU_Utilization
order by record_id DESC
) AS D
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaCPUAgarradosTable = REPLACE( REPLACE( REPLACE( @AlertaCPUAgarradosTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaCPUAgarradosTable =
''
+ '
SQL Server (%) |
Outros Processos (%) |
Livre (%) |
Utilização Total (%) |
'
+ REPLACE( REPLACE( @AlertaCPUAgarradosTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveHeader = ''
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ' Processos executando no Banco de Dados '
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveTable = 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 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
) AS D ORDER BY [start_time]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @ResultadoWhoisactiveTable = REPLACE( REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @ResultadoWhoisactiveTable =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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: O Consumo de CPU está acima de ' + CAST((@CPU_Parametro) AS VARCHAR) + '% no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaCPUAgarradosHeader + @EmptyBodyEmail + @AlertaCPUAgarradosTable + @EmptyBodyEmail +
@ResultadoWhoisactiveHeader + @EmptyBodyEmail + @ResultadoWhoisactiveTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END
END
END -- FIM - ALERTA
ELSE
BEGIN -- INICIO - CLEAR
IF @Fl_Tipo = 1
BEGIN
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - DADOS - 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
/*******************************************************************************************************************************
-- CRIA O EMAIL - CLEAR
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaCPUAgarradosHeader = ''
SET @AlertaCPUAgarradosHeader = @AlertaCPUAgarradosHeader + ' Consumo de CPU no Servidor '
SET @AlertaCPUAgarradosHeader = @AlertaCPUAgarradosHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaCPUAgarradosTable = CAST( (
SELECT td = [SQLProcessUtilization] + ' | '
+ '' + OtherProcessUtilization + ' | '
+ '' + [SystemIdle] + ' | '
+ '' + CPU_Utilization + ' | '
FROM (
-- Dados da Tabela do EMAIL
select TOP 1
CAST([SQLProcessUtilization] AS VARCHAR) [SQLProcessUtilization],
CAST((100 - SystemIdle - SQLProcessUtilization) AS VARCHAR) as OtherProcessUtilization,
CAST([SystemIdle] AS VARCHAR) AS [SystemIdle],
CAST(100 - SystemIdle AS VARCHAR) AS CPU_Utilization
from #CPU_Utilization
order by record_id DESC
) AS D
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaCPUAgarradosTable = REPLACE( REPLACE( REPLACE( @AlertaCPUAgarradosTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaCPUAgarradosTable =
''
+ '
SQL Server (%) |
Outros Processos (%) |
Livre (%) |
Utilização Total (%) |
'
+ REPLACE( REPLACE( @AlertaCPUAgarradosTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveHeader = ''
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ' Processos executando no Banco de Dados '
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveTable = 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 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
) AS D ORDER BY [start_time]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @ResultadoWhoisactiveTable = REPLACE( REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @ResultadoWhoisactiveTable =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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: O Consumo de CPU está abaixo de ' + CAST((@CPU_Parametro) AS VARCHAR) + '% no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaCPUAgarradosHeader + @EmptyBodyEmail + @AlertaCPUAgarradosTable + @EmptyBodyEmail +
@ResultadoWhoisactiveHeader + @EmptyBodyEmail + @ResultadoWhoisactiveTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - CLEAR
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 0
END
END -- FIM - CLEAR
END
GO
/*
IF ( OBJECT_ID('[dbo].[stpAlerta_MaxSize_Arquivo_SQL]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_MaxSize_Arquivo_SQL]
GO
/*******************************************************************************************************************************
-- ALERTA: MAXSIZE ARQUIVO SQL
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_MaxSize_Arquivo_SQL]
AS
BEGIN
SET NOCOUNT ON
-- MaxSize Arquivo SQL
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'MaxSize Arquivo SQL')
-- Cria as tabelas que irão armazenar as informações sobre os arquivos
IF ( OBJECT_ID('tempdb..##MDFs_Sizes_Alertas') IS NOT NULL )
DROP TABLE ##MDFs_Sizes_Alertas
CREATE TABLE ##MDFs_Sizes_Alertas(
[Server] VARCHAR(50),
[Nm_Database] VARCHAR(100),
[NomeLogico] VARCHAR(100),
[Total_Utilizado] NUMERIC(15,2),
[Espaco_Livre (MB)] NUMERIC(15,2),
[physical_name] VARCHAR(4000)
)
IF ( OBJECT_ID('tempdb..#Logs_Sizes') IS NOT NULL )
DROP TABLE #Logs_Sizes
CREATE TABLE #Logs_Sizes(
[Server] VARCHAR(50),
[Nm_Database] VARCHAR(100) NOT NULL,
[Log_Size(KB)] BIGINT NOT NULL,
[Log_Used(KB)] BIGINT NOT NULL,
[Log_Used(%)] DECIMAL(22, 2) NULL
)
-- Popula os dados
EXEC sp_MSforeachdb '
Use [?]
INSERT INTO ##MDFs_Sizes_Alertas
SELECT @@SERVERNAME,
db_name() AS NomeBase,
[Name] AS NomeLogico,
CONVERT(DECIMAL(15,2), ROUND(FILEPROPERTY(a.[Name], ''SpaceUsed'') / 128.000, 2)) AS [Total_Utilizado (MB)],
CONVERT(DECIMAL(15,2), ROUND((a.Size-FILEPROPERTY(a.[Name], ''SpaceUsed'')) / 128.000, 2)) AS [Available Space (MB)],
[Filename] AS physical_name
FROM [dbo].[sysfiles] a (NOLOCK)
JOIN [sysfilegroups] b (NOLOCK) ON a.[groupid] = b.[groupid]
ORDER BY b.[groupname]'
-- Busca as informações sobre os arquivos LDF
INSERT INTO #Logs_Sizes([Server], [Nm_Database], [Log_Size(KB)], [Log_Used(KB)], [Log_Used(%)])
SELECT @@SERVERNAME,
db.[name] AS [Database Name],
ls.[cntr_value] AS [Log Size (KB)] ,
lu.[cntr_value] AS [Log Used (KB)] ,
CAST( CAST( lu.[cntr_value] AS FLOAT) /
CASE WHEN CAST(ls.[cntr_value] AS FLOAT) = 0
THEN 1
ELSE CAST(ls.[cntr_value] AS FLOAT)
END AS DECIMAL(18,2)) * 100 AS [Log Used %]
FROM [sys].[databases] AS db WITH(NOLOCK)
JOIN [sys].[dm_os_performance_counters] AS lu WITH(NOLOCK) ON db.[name] = lu.[instance_name]
JOIN [sys].[dm_os_performance_counters] AS ls WITH(NOLOCK) ON db.[name] = ls.[instance_name]
WHERE lu.[counter_name] LIKE 'Log File(s) Used Size (KB)%'
AND ls.[counter_name] LIKE 'Log File(s) Size (KB)%'
-- Cria a tabela com os dados resumidos
IF ( OBJECT_ID('_Resultado_Alerta_SQLFile') IS NOT NULL )
DROP TABLE _Resultado_Alerta_SQLFile
SELECT @@SERVERNAME AS [Server],
DB_NAME(A.[database_id]) AS [Nm_Database],
[name] AS Logical_Name,
CASE WHEN RIGHT(A.[physical_name], 3) = 'mdf' OR RIGHT(A.[physical_name], 3) = 'ndf'
THEN B.[Total_Utilizado]
ELSE (C.[Log_Used(KB)]) / 1024.0
END AS [Used(MB)],
(
( CASE WHEN A.[Max_Size] = -1
THEN -1
ELSE ( A.[Max_Size] / 1024 ) * 8
END ) - ( CASE WHEN [is_percent_growth] = 1
THEN ( ( A.[Max_Size] / 1024 ) * 8 ) * ((A.[Growth] / 100.00))
ELSE CAST(( A.[Growth] * 8 ) / 1024.00 AS NUMERIC(15, 2))
END )
) * .85 -
CASE WHEN RIGHT(A.[physical_name], 3) = 'mdf' OR RIGHT(A.[physical_name], 3) = 'ndf'
THEN B.[Total_Utilizado]
ELSE (C.[Log_Used(KB)]) / 1024.0
END AS [Alerta],
CASE WHEN A.[name] = 'tempdev'
THEN ([Espaco_Livre (MB)] + [Total_Utilizado])
ELSE ([Size] / 1024.0) * 8
END AS [Size(MB)],
CASE WHEN RIGHT(A.[physical_name], 3) = 'mdf' OR RIGHT(A.[physical_name], 3) = 'ndf'
THEN [Espaco_Livre (MB)]
ELSE ([Log_Size(KB)] - [Log_Used(KB)]) / 1024.0
END AS [Free_Space(MB)],
CASE WHEN A.[name] = 'tempdev'
THEN ([Espaco_Livre (MB)] / ([Espaco_Livre (MB)] + [Total_Utilizado])) * 100.00
WHEN RIGHT(A.[physical_name], 3) = 'mdf' OR RIGHT(A.[physical_name], 3) = 'ndf'
THEN (([Espaco_Livre (MB)] / ((Size/1024.0) * 8.0))) * 100.0
ELSE (100.00 - C.[Log_Used(%)])
END AS [Free_Space(%)],
CASE WHEN A.[Max_Size] = -1
THEN -1
ELSE (A.[Max_Size] / 1024) * 8
END AS [MaxSize(MB)],
CASE WHEN [is_percent_growth] = 1
THEN CAST(A.[Growth] AS VARCHAR) + ' %'
ELSE CAST (CAST((A.[Growth] * 8) / 1024.00 AS NUMERIC(15, 2)) AS VARCHAR) + ' MB'
END AS [Growth]
INTO [dbo].[_Resultado_Alerta_SQLFile]
FROM [sys].[master_files] A WITH(NOLOCK)
JOIN ##MDFs_Sizes_Alertas B ON A.[physical_name] = B.[physical_name]
JOIN #Logs_Sizes C ON C.[Nm_Database] = db_name(A.[database_id])
WHERE A.[type_desc] <> 'FULLTEXT'
AND A.[Max_Size] NOT IN ( -1 )
AND CASE WHEN A.[Max_Size] = -1 THEN -1
ELSE ( A.[Max_Size] / 1024 ) * 8
END <> 2097152
-- Declara as variaveis
DECLARE @Subject VARCHAR(500), @Fl_Tipo TINYINT, @Importance AS VARCHAR(6), @EmailBody VARCHAR(MAX), @Maxsize_Parametro INT,
@AlertaMDFLDFHeader VARCHAR(MAX), @AlertaMDFLDFTable VARCHAR(MAX), @EmptyBodyEmail VARCHAR(MAX), @EmailDestination VARCHAR(500),
@ProfileEmail VARCHAR(200)
-- 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 [Id_Alerta_Parametro] = @Id_Alerta_Parametro )
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
SELECT @Maxsize_Parametro = Vl_Parametro * 1000, -- Tamanho (MB)
@EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- MaxSize Arquivo SQL
/*******************************************************************************************************************************
-- Verifica se existe algum arquivo MDF ou LDF próximo do MaxSize
*******************************************************************************************************************************/
IF EXISTS (
SELECT NULL
FROM [dbo].[_Resultado_Alerta_SQLFile]
WHERE (
CASE WHEN [MaxSize(MB)] >= 150000
THEN CASE WHEN (([MaxSize(MB)] - [Used(MB)]) < @Maxsize_Parametro)
THEN 1
ELSE 0
END
ELSE CASE WHEN [Alerta] < 0
THEN 1
ELSE 0
END
END
) = 1
)
BEGIN -- INICIO - ALERTA
IF ISNULL(@Fl_Tipo, 0) = 0 -- Envia o Alerta apenas uma vez
BEGIN
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaMDFLDFHeader = ''
SET @AlertaMDFLDFHeader = @AlertaMDFLDFHeader + ' Informações arquivos .LDF e .MDF com "MaxSize" especificado '
SET @AlertaMDFLDFHeader = @AlertaMDFLDFHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaMDFLDFTable = CAST( (
SELECT td = [Nm_Database] + ' | '
+ '' + [Logical_Name] + ' | '
+ '' + [Tamanho_Atual (MB)] + ' | '
+ '' + [Livre (MB)] + ' | '
+ '' + [Utilizado (MB)] + ' | '
+ '' + [MaxSize(MB)] + ' | '
+ '' + [Growth] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT DISTINCT
[Nm_Database],
[Logical_Name],
CAST([Size(MB)] AS VARCHAR) AS [Tamanho_Atual (MB)],
CAST([Free_Space(MB)] AS VARCHAR) AS [Livre (MB)],
CAST([Used(MB)] AS VARCHAR) AS [Utilizado (MB)],
CAST([MaxSize(MB)] AS VARCHAR) AS [MaxSize(MB)],
[Growth]
FROM [Traces].[dbo].[_Resultado_Alerta_SQLFile]
WHERE (
CASE WHEN [MaxSize(MB)] >= 150000
THEN CASE WHEN (([MaxSize(MB)] - [Used(MB)]) < 15000)
THEN 1
ELSE 0
END
ELSE CASE WHEN [Alerta] < 0
THEN 1
ELSE 0
END
END
) = 1
) AS D ORDER BY [Livre (MB)]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaMDFLDFTable = REPLACE( REPLACE( REPLACE( @AlertaMDFLDFTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaMDFLDFTable =
''
+ '
Database |
Nome Lógico |
Tamanho Atual (MB) |
Livre (MB) |
Utilizado (MB) |
MaxSize (MB) |
Crescimento |
'
+ REPLACE( REPLACE( @AlertaMDFLDFTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- Insere um Espaço em Branco no EMAIL
--------------------------------------------------------------------------------------------------------------------------------
SET @EmptyBodyEmail = ''
SET @EmptyBodyEmail =
'' +
'
|
'
+ REPLACE( REPLACE( ISNULL(@EmptyBodyEmail,''), '<', '<'), '>', '>')
+ ' '
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
SELECT @Importance = 'High',
@Subject = 'ALERTA: Existe algum arquivo MDF ou LDF com risco de estouro do Maxsize no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaMDFLDFHeader + @EmptyBodyEmail + @AlertaMDFLDFTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END
END -- FIM - ALERTA
ELSE
BEGIN -- INICIO - CLEAR
IF @Fl_Tipo = 1
BEGIN
/*******************************************************************************************************************************
-- CRIA O EMAIL - CLEAR
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaMDFLDFHeader = ''
SET @AlertaMDFLDFHeader = @AlertaMDFLDFHeader + ' Informações arquivos .LDF e .MDF com "MaxSize" especificado '
SET @AlertaMDFLDFHeader = @AlertaMDFLDFHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaMDFLDFTable = CAST( (
SELECT td = [Nm_Database] + ' | '
+ '' + [Logical_Name] + ' | '
+ '' + [Tamanho_Atual (MB)] + ' | '
+ '' + [Livre (MB)] + ' | '
+ '' + [Utilizado (MB)] + ' | '
+ '' + [MaxSize(MB)] + ' | '
+ '' + [Growth] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT DISTINCT
[Nm_Database],
[Logical_Name],
CAST([Size(MB)] AS VARCHAR) AS [Tamanho_Atual (MB)],
CAST([Free_Space(MB)] AS VARCHAR) AS [Livre (MB)],
CAST([Used(MB)] AS VARCHAR) AS [Utilizado (MB)],
CAST([MaxSize(MB)] AS VARCHAR) AS [MaxSize(MB)],
[Growth]
FROM [Traces].[dbo].[_Resultado_Alerta_SQLFile]
) AS D ORDER BY [Utilizado (MB)] DESC
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaMDFLDFTable = REPLACE( REPLACE( REPLACE( @AlertaMDFLDFTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaMDFLDFTable =
''
+ '
Database |
Nome Lógico |
Tamanho Atual (MB) |
Livre (MB) |
Utilizado (MB) |
MaxSize (MB) |
Crescimento |
'
+ REPLACE( REPLACE( @AlertaMDFLDFTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- Insere um Espaço em Branco no EMAIL
--------------------------------------------------------------------------------------------------------------------------------
SET @EmptyBodyEmail = ''
SET @EmptyBodyEmail =
'' +
'
|
'
+ REPLACE( REPLACE( ISNULL(@EmptyBodyEmail,''), '<', '<'), '>', '>')
+ ' '
/*******************************************************************************************************************************
-- ENVIA O EMAIL - CLEAR
*******************************************************************************************************************************/
SELECT @Importance = 'High',
@Subject = 'CLEAR: Não existe mais algum arquivo MDF ou LDF com risco de estouro do Maxsize no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaMDFLDFHeader + @EmptyBodyEmail + @AlertaMDFLDFTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 0
END
END -- FIM - CLEAR
END
*/
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_Tempdb_Utilizacao_Arquivo_MDF]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_Tempdb_Utilizacao_Arquivo_MDF]
GO
/*******************************************************************************************************************************
-- ALERTA: TEMPDB UTILIZACAO ARQUIVO MDF
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_Tempdb_Utilizacao_Arquivo_MDF]
AS
BEGIN
SET NOCOUNT ON
-- Tamanho Arquivo MDF Tempdb
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Tempdb Utilizacao Arquivo MDF')
declare @Tempo_Conexoes_Hs tinyint, @Tempdb_Parametro int, @EmailDestination VARCHAR(500), @Tamanho_Tempdb INT, @ProfileEmail VARCHAR(200)
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
SELECT @Tempdb_Parametro = Vl_Parametro, -- Percentual
@EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Tempdb Utilizacao Arquivo
-- Conexões mais antigas que 1 hora
SELECT @Tempo_Conexoes_Hs = 1,
@Tamanho_Tempdb = 10000 -- 10 GB
-- Declara as variaveis
DECLARE @Subject VARCHAR(500), @Fl_Tipo TINYINT, @Importance AS VARCHAR(6), @EmailBody VARCHAR(MAX), @AlertaTamanhoMDFTempdbHeader VARCHAR(MAX),
@AlertaTamanhoMDFTempdbTable VARCHAR(MAX), @AlertaTempdbUtilizacaoArquivoHeader VARCHAR(MAX), @AlertaTamanhoMDFTempdbConexoesTable VARCHAR(MAX),
@EmptyBodyEmail VARCHAR(MAX), @AlertaTempdbProcessoExecHeader VARCHAR(MAX), @AlertaTempdbProcessoExecTable VARCHAR(MAX), @Dt_Atual DATETIME
-- 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 [Id_Alerta_Parametro] = @Id_Alerta_Parametro )
-- Busca as informações do Tempdb
IF ( OBJECT_ID('tempdb..#Alerta_Tamanho_MDF_Tempdb') IS NOT NULL )
DROP TABLE #Alerta_Tamanho_MDF_Tempdb
select
file_id,
reserved_MB = CAST((unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count +
internal_object_reserved_page_count+mixed_extent_page_count)*8/1024. AS numeric(15,2)) ,
unallocated_extent_MB = CAST(unallocated_extent_page_count*8/1024. AS NUMERIC(15,2)),
internal_object_reserved_MB = CAST(internal_object_reserved_page_count*8/1024. AS NUMERIC(15,2)),
version_store_reserved_MB = CAST(version_store_reserved_page_count*8/1024. AS NUMERIC(15,2)),
user_object_reserved_MB = convert(numeric(10,2),round(user_object_reserved_page_count*8/1024.,2))
into #Alerta_Tamanho_MDF_Tempdb
from tempdb.sys.dm_db_file_space_usage
IF ( OBJECT_ID('tempdb..#Alerta_Tamanho_MDF_Tempdb_Conexoes') IS NOT NULL )
DROP TABLE #Alerta_Tamanho_MDF_Tempdb_Conexoes
-- Busca as transações que estão abertas
CREATE TABLE #Alerta_Tamanho_MDF_Tempdb_Conexoes(
[session_id] [smallint] NULL,
[login_time] [varchar](40) NULL,
[login_name] [nvarchar](128) NULL,
[host_name] [nvarchar](128) NULL,
[open_transaction_Count] [int] NULL,
[status] [nvarchar](30) NULL,
[cpu_time] [int] NULL,
[total_elapsed_time] [int] NULL,
[reads] [bigint] NULL,
[writes] [bigint] NULL,
[logical_reads] [bigint] NULL
) ON [PRIMARY]
-- Query Alerta Tempdb - Conexões abertas - Incluir no Alerta TempDb
INSERT INTO #Alerta_Tamanho_MDF_Tempdb_Conexoes
SELECT TOP 50 session_id, convert(varchar(20),login_time,120) AS login_time, login_name, host_name,
/*open_transaction_Count,*/ NULL, status, cpu_time, total_elapsed_time, reads, writes, logical_reads
FROM sys.dm_exec_sessions
WHERE session_id > 50
--and open_transaction_Count > 0
and dateadd(hour,-@Tempo_Conexoes_Hs,getdate()) > login_time
ORDER BY logical_reads DESC
-- Tratamento caso não retorne nenhuma conexão
IF NOT EXISTS (SELECT TOP 1 session_id FROM #Alerta_Tamanho_MDF_Tempdb_Conexoes)
BEGIN
INSERT INTO #Alerta_Tamanho_MDF_Tempdb_Conexoes
VALUES(NULL, 'Sem conexao aberta a mais de 1 hora', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
END
--------------------------------------------------------------------------------------------------------------------------------
-- 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 se o Consumo do Arquivo do Tempdb está muito grande
*******************************************************************************************************************************/
IF EXISTS (
select TOP 1 unallocated_extent_MB
from #Alerta_Tamanho_MDF_Tempdb
where reserved_MB > @Tamanho_Tempdb
and unallocated_extent_MB < reserved_MB * (1 - (@Tempdb_Parametro / 100.0))
)
BEGIN -- INICIO - ALERTA
IF ISNULL(@Fl_Tipo, 0) = 0 -- Envia o Alerta apenas uma vez
BEGIN
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA - TAMANHO ARQUIVO MDF TEMPDB
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaTamanhoMDFTempdbHeader = ''
SET @AlertaTamanhoMDFTempdbHeader = @AlertaTamanhoMDFTempdbHeader + ' Tamanho Arquivo MDF Tempdb '
SET @AlertaTamanhoMDFTempdbHeader = @AlertaTamanhoMDFTempdbHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaTamanhoMDFTempdbTable = CAST( (
SELECT td = CAST(file_id AS VARCHAR) + ' | '
+ '' + CAST(reserved_MB AS VARCHAR) + ' | '
+ '' + CAST(Pr_Utilizado AS VARCHAR) + ' | '
+ '' + CAST(unallocated_extent_MB AS VARCHAR) + ' | '
+ '' + CAST(internal_object_reserved_MB AS VARCHAR) + ' | '
+ '' + CAST(version_store_reserved_MB AS VARCHAR) + ' | '
+ '' + CAST(user_object_reserved_MB AS VARCHAR) + ' | '
FROM (
-- Dados da Tabela do EMAIL
select file_id,
reserved_MB,
CAST( ((1 - (unallocated_extent_MB / reserved_MB)) * 100) AS NUMERIC(15,2)) AS Pr_Utilizado,
unallocated_extent_MB,
internal_object_reserved_MB,
version_store_reserved_MB,
user_object_reserved_MB
from #Alerta_Tamanho_MDF_Tempdb
) AS D
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaTamanhoMDFTempdbTable = REPLACE( REPLACE( REPLACE( @AlertaTamanhoMDFTempdbTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaTamanhoMDFTempdbTable =
''
+ '
File ID |
Espaço Reservado (MB) |
Percentual Utilizado (%) |
Espaço Não Alocado (MB) |
Espaço Objetos Internos (MB) |
Espaço Version Store (MB) |
Espaço Objetos de Usuário (MB) |
'
+ REPLACE( REPLACE( @AlertaTamanhoMDFTempdbTable, '<', '<'), '>', '>')
+ ' '
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA - CONEXOES COM TRANSACAO ABERTA
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaTempdbUtilizacaoArquivoHeader = ''
SET @AlertaTempdbUtilizacaoArquivoHeader = @AlertaTempdbUtilizacaoArquivoHeader + ' TOP 50 - Conexões com Transação Aberta '
SET @AlertaTempdbUtilizacaoArquivoHeader = @AlertaTempdbUtilizacaoArquivoHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaTamanhoMDFTempdbConexoesTable = CAST( (
SELECT td = session_id + ' | '
+ '' + login_time + ' | '
+ '' + login_name + ' | '
+ '' + host_name + ' | '
+ '' + open_transaction_Count + ' | '
+ '' + status + ' | '
+ '' + cpu_time + ' | '
+ '' + total_elapsed_time + ' | '
+ '' + reads + ' | '
+ '' + writes + ' | '
+ '' + logical_reads + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT ISNULL(CAST(session_id AS VARCHAR), '-') AS session_id,
ISNULL(login_time, '-') AS login_time,
ISNULL(login_name, '-') AS login_name,
ISNULL(host_name, '-') AS host_name,
ISNULL(CAST(open_transaction_Count AS VARCHAR),'-') AS open_transaction_Count,
ISNULL(status, '-') AS status,
ISNULL(CAST(cpu_time AS VARCHAR),'-') AS cpu_time,
ISNULL(CAST(total_elapsed_time AS VARCHAR),'-') AS total_elapsed_time,
ISNULL(CAST(reads AS VARCHAR),'-') AS reads,
ISNULL(CAST(writes AS VARCHAR),'-') AS writes,
ISNULL(CAST(logical_reads AS VARCHAR),'-') AS logical_reads
FROM #Alerta_Tamanho_MDF_Tempdb_Conexoes
) AS D ORDER BY CAST(REPLACE([logical_reads], '-', 0) AS BIGINT) DESC
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaTamanhoMDFTempdbConexoesTable = REPLACE( REPLACE( REPLACE( @AlertaTamanhoMDFTempdbConexoesTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaTamanhoMDFTempdbConexoesTable =
''
+ '
session_id |
login_time |
login_name |
host_name |
open_transaction_Count |
status |
cpu_time |
total_elapsed_time |
reads |
writes |
logical_reads |
'
+ REPLACE( REPLACE( @AlertaTamanhoMDFTempdbConexoesTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaTempdbProcessoExecHeader = ''
SET @AlertaTempdbProcessoExecHeader = @AlertaTempdbProcessoExecHeader + ' TOP 50 - Processos executando no Banco de Dados '
SET @AlertaTempdbProcessoExecHeader = @AlertaTempdbProcessoExecHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaTempdbProcessoExecTable = 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 @AlertaTempdbProcessoExecTable = REPLACE( REPLACE( REPLACE( @AlertaTempdbProcessoExecTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaTempdbProcessoExecTable =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @AlertaTempdbProcessoExecTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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: O Tamanho do Arquivo MDF do Tempdb está acima de 70% no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaTamanhoMDFTempdbHeader + @EmptyBodyEmail + @AlertaTamanhoMDFTempdbTable + @EmptyBodyEmail +
@AlertaTempdbUtilizacaoArquivoHeader + @EmptyBodyEmail + @AlertaTamanhoMDFTempdbConexoesTable + @EmptyBodyEmail +
@AlertaTempdbProcessoExecHeader + @EmptyBodyEmail + @AlertaTempdbProcessoExecTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END
END -- FIM - ALERTA
ELSE
BEGIN -- INICIO - CLEAR
IF @Fl_Tipo = 1
BEGIN
/*******************************************************************************************************************************
-- CRIA O EMAIL - CLEAR - TAMANHO ARQUIVO MDF TEMPDB
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaTamanhoMDFTempdbHeader = ''
SET @AlertaTamanhoMDFTempdbHeader = @AlertaTamanhoMDFTempdbHeader + ' Tamanho Arquivo MDF Tempdb '
SET @AlertaTamanhoMDFTempdbHeader = @AlertaTamanhoMDFTempdbHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaTamanhoMDFTempdbTable = CAST( (
SELECT td = CAST(file_id AS VARCHAR) + ' | '
+ '' + CAST(reserved_MB AS VARCHAR) + ' | '
+ '' + CAST(Pr_Utilizado AS VARCHAR) + ' | '
+ '' + CAST(unallocated_extent_MB AS VARCHAR) + ' | '
+ '' + CAST(internal_object_reserved_MB AS VARCHAR) + ' | '
+ '' + CAST(version_store_reserved_MB AS VARCHAR) + ' | '
+ '' + CAST(user_object_reserved_MB AS VARCHAR) + ' | '
FROM (
-- Dados da Tabela do EMAIL
select file_id,
reserved_MB,
CAST( ((1 - (unallocated_extent_MB / reserved_MB)) * 100) AS NUMERIC(15,2)) AS Pr_Utilizado,
unallocated_extent_MB,
internal_object_reserved_MB,
version_store_reserved_MB,
user_object_reserved_MB
from #Alerta_Tamanho_MDF_Tempdb
) AS D
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaTamanhoMDFTempdbTable = REPLACE( REPLACE( REPLACE( @AlertaTamanhoMDFTempdbTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaTamanhoMDFTempdbTable =
''
+ '
File ID |
Espaço Reservado (MB) |
Percentual Utilizado (%) |
Espaço Não Alocado (MB) |
Espaço Objetos Internos (MB) |
Espaço Version Store (MB) |
Espaço Objetos de Usuário (MB) |
'
+ REPLACE( REPLACE( @AlertaTamanhoMDFTempdbTable, '<', '<'), '>', '>')
+ ' '
/*******************************************************************************************************************************
-- CRIA O EMAIL - CLEAR - CONEXOES COM TRANSACAO ABERTA
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaTempdbUtilizacaoArquivoHeader = ''
SET @AlertaTempdbUtilizacaoArquivoHeader = @AlertaTempdbUtilizacaoArquivoHeader + ' TOP 50 - Conexões com Transação Aberta '
SET @AlertaTempdbUtilizacaoArquivoHeader = @AlertaTempdbUtilizacaoArquivoHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaTamanhoMDFTempdbConexoesTable = CAST( (
SELECT td = session_id + ' | '
+ '' + login_time + ' | '
+ '' + login_name + ' | '
+ '' + host_name + ' | '
+ '' + open_transaction_Count + ' | '
+ '' + status + ' | '
+ '' + cpu_time + ' | '
+ '' + total_elapsed_time + ' | '
+ '' + reads + ' | '
+ '' + writes + ' | '
+ '' + logical_reads + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT ISNULL(CAST(session_id AS VARCHAR), '-') AS session_id,
ISNULL(login_time, '-') AS login_time,
ISNULL(login_name, '-') AS login_name,
ISNULL(host_name, '-') AS host_name,
ISNULL(CAST(open_transaction_Count AS VARCHAR),'-') AS open_transaction_Count,
ISNULL(status, '-') AS status,
ISNULL(CAST(cpu_time AS VARCHAR),'-') AS cpu_time,
ISNULL(CAST(total_elapsed_time AS VARCHAR),'-') AS total_elapsed_time,
ISNULL(CAST(reads AS VARCHAR),'-') AS reads,
ISNULL(CAST(writes AS VARCHAR),'-') AS writes,
ISNULL(CAST(logical_reads AS VARCHAR),'-') AS logical_reads
FROM #Alerta_Tamanho_MDF_Tempdb_Conexoes
) AS D ORDER BY CAST(REPLACE([logical_reads], '-', 0) AS BIGINT) DESC
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaTamanhoMDFTempdbConexoesTable = REPLACE( REPLACE( REPLACE( @AlertaTamanhoMDFTempdbConexoesTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaTamanhoMDFTempdbConexoesTable =
''
+ '
session_id |
login_time |
login_name |
host_name |
open_transaction_Count |
status |
cpu_time |
total_elapsed_time |
reads |
writes |
logical_reads |
'
+ REPLACE( REPLACE( @AlertaTamanhoMDFTempdbConexoesTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaTempdbProcessoExecHeader = ''
SET @AlertaTempdbProcessoExecHeader = @AlertaTempdbProcessoExecHeader + ' TOP 50 - Processos executando no Banco de Dados '
SET @AlertaTempdbProcessoExecHeader = @AlertaTempdbProcessoExecHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaTempdbProcessoExecTable = 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 @AlertaTempdbProcessoExecTable = REPLACE( REPLACE( REPLACE( @AlertaTempdbProcessoExecTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaTempdbProcessoExecTable =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @AlertaTempdbProcessoExecTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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: O Tamanho do Arquivo MDF do Tempdb está abaixo de 70% no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaTamanhoMDFTempdbHeader + @EmptyBodyEmail + @AlertaTamanhoMDFTempdbTable + @EmptyBodyEmail +
@AlertaTempdbUtilizacaoArquivoHeader + @EmptyBodyEmail + @AlertaTamanhoMDFTempdbConexoesTable + @EmptyBodyEmail+
@AlertaTempdbProcessoExecHeader + @EmptyBodyEmail + @AlertaTempdbProcessoExecTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - CLEAR
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 0
END
END -- FIM - CLEAR
END
GO
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_Conexao_SQLServer]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_Conexao_SQLServer]
GO
/*******************************************************************************************************************************
-- ALERTA: CONEXAO SQL SERVER
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_Conexao_SQLServer]
AS
BEGIN
SET NOCOUNT ON
-- Conexões SQL Server
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Conexão SQL Server')
-- Declara as variaveis
DECLARE @Dt_Atual DATETIME, @EmailBody VARCHAR(MAX), @AlertaConexaoSQLServerHeader VARCHAR(MAX), @AlertaConexaoSQLServerTable VARCHAR(MAX),
@EmptyBodyEmail VARCHAR(MAX), @Importance AS VARCHAR(6), @Subject VARCHAR(500), @Qtd_Conexoes INT, @Conexoes_SQLServer_Parametro INT,
@Fl_Tipo INT, @EmailDestination VARCHAR(500), @AlertaConexaoProcessosExecHeader VARCHAR(MAX), @AlertaConexaoProcessosExecTable VARCHAR(MAX),
@ProfileEmail VARCHAR(200)
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
SELECT @Conexoes_SQLServer_Parametro = Vl_Parametro, -- Quantidade
@EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Conexões SQL Server
SELECT @Qtd_Conexoes = count(*) FROM sys.dm_exec_sessions WHERE session_id > 50
-- 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 [Id_Alerta_Parametro] = @Id_Alerta_Parametro )
--------------------------------------------------------------------------------------------------------------------------------
-- 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 se o limite de conexões para o Alerta foi atingido
*******************************************************************************************************************************/
IF (@Qtd_Conexoes > @Conexoes_SQLServer_Parametro)
BEGIN -- INICIO - ALERTA
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - CONEXÕES - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaConexaoSQLServerHeader = ''
SET @AlertaConexaoSQLServerHeader = @AlertaConexaoSQLServerHeader + ' TOP 25 - Conexões Abertas no SQL Server '
SET @AlertaConexaoSQLServerHeader = @AlertaConexaoSQLServerHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - CONEXÕES - BODY
--------------------------------------------------------------------------------------------------------------------------------
if object_id('tempdb..#ConexoesAbertas') is not null
drop table #ConexoesAbertas
SELECT TOP 25 IDENTITY(INT, 1, 1) AS id,
replace(replace(ec.client_net_address,'<',''),'>','') client_net_address,
case when es.[program_name] = '' then 'Sem nome na string de conexão' else [program_name] end [program_name],
es.[host_name], es.login_name, /*db_name(database_id)*/ '' Base,
COUNT(ec.session_id) AS [connection count]
into #ConexoesAbertas
FROM sys.dm_exec_sessions AS es
INNER JOIN sys.dm_exec_connections AS ec ON es.session_id = ec.session_id
GROUP BY ec.client_net_address, es.[program_name], es.[host_name],/*db_name(database_id),*/ es.login_name
order by [connection count] desc
SET @AlertaConexaoSQLServerTable = CAST( (
SELECT td = client_net_address + ' | '
+ '' + [program_name] + ' | '
+ '' + [host_name] + ' | '
+ '' + login_name + ' | '
+ '' + Base + ' | '
+ '' + [connection count] + ' | '
FROM (
SELECT client_net_address,
[program_name],
[host_name], login_name, Base,
cast([connection count] as varchar) [connection count] ,id
FROM #ConexoesAbertas
) AS D ORDER BY id
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaConexaoSQLServerTable = REPLACE( REPLACE( REPLACE( @AlertaConexaoSQLServerTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaConexaoSQLServerTable =
''
+ '
IP |
Aplicacao |
Hostname |
Login |
Database |
Qtd. Conexões |
'
+ REPLACE( REPLACE( @AlertaConexaoSQLServerTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaConexaoProcessosExecHeader = ''
SET @AlertaConexaoProcessosExecHeader = @AlertaConexaoProcessosExecHeader + ' TOP 50 - Processos executando no Banco de Dados '
SET @AlertaConexaoProcessosExecHeader = @AlertaConexaoProcessosExecHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaConexaoProcessosExecTable = 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 @AlertaConexaoProcessosExecTable = REPLACE( REPLACE( REPLACE( @AlertaConexaoProcessosExecTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaConexaoProcessosExecTable =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @AlertaConexaoProcessosExecTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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: Existem ' + cast(@Qtd_Conexoes as varchar) + ' Conexões Abertas no SQL Server no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaConexaoSQLServerHeader + @EmptyBodyEmail + @AlertaConexaoSQLServerTable + @EmptyBodyEmail +
@AlertaConexaoProcessosExecHeader + @EmptyBodyEmail + @AlertaConexaoProcessosExecTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END -- FIM - ALERTA
ELSE
BEGIN -- INICIO - CLEAR
IF @Fl_Tipo = 1
BEGIN
/*******************************************************************************************************************************
-- CRIA O EMAIL - CLEAR
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaConexaoSQLServerHeader = ''
SET @AlertaConexaoSQLServerHeader = @AlertaConexaoSQLServerHeader + ' Conexões abertas no SQL Server '
SET @AlertaConexaoSQLServerHeader = @AlertaConexaoSQLServerHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - BODY
--------------------------------------------------------------------------------------------------------------------------------
if object_id('tempdb..#ConexoesAbertas_Clear') is not null
drop table #ConexoesAbertas_Clear
SELECT top 25 IDENTITY(INT, 1, 1) AS id,
replace(replace(ec.client_net_address,'<',''),'>','') client_net_address,
case when es.[program_name] = '' then 'Sem nome na string de conexão' else [program_name] end [program_name],
es.[host_name], es.login_name, /*db_name(database_id)*/ '' Base,
COUNT(ec.session_id) AS [connection count]
into #ConexoesAbertas_Clear
FROM sys.dm_exec_sessions AS es
INNER JOIN sys.dm_exec_connections AS ec
ON es.session_id = ec.session_id
GROUP BY ec.client_net_address, es.[program_name], es.[host_name],/*db_name(database_id),*/ es.login_name
order by [connection count] desc
SET @AlertaConexaoSQLServerTable = CAST( (
SELECT td = client_net_address + ' | '
+ '' + [program_name] + ' | '
+ '' + [host_name] + ' | '
+ '' + login_name + ' | '
+ '' + Base + ' | '
+ '' + [connection count] + ' | '
FROM (
SELECT client_net_address,
[program_name],
[host_name], login_name, Base,
cast([connection count] as varchar) [connection count] ,id
FROM #ConexoesAbertas_Clear
) AS D ORDER BY id
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaConexaoSQLServerTable = REPLACE( REPLACE( REPLACE( @AlertaConexaoSQLServerTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaConexaoSQLServerTable =
''
+ '
IP |
Aplicacao |
Hostname |
Login |
Database |
Qtd. Conexões |
'
+ REPLACE( REPLACE( @AlertaConexaoSQLServerTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaConexaoProcessosExecHeader = ''
SET @AlertaConexaoProcessosExecHeader = @AlertaConexaoProcessosExecHeader + ' TOP 50 - Processos executando no Banco de Dados '
SET @AlertaConexaoProcessosExecHeader = @AlertaConexaoProcessosExecHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaConexaoProcessosExecTable = 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 @AlertaConexaoProcessosExecTable = REPLACE( REPLACE( REPLACE( @AlertaConexaoProcessosExecTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaConexaoProcessosExecTable =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @AlertaConexaoProcessosExecTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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: Existem ' + cast(@Qtd_Conexoes as varchar) + ' Conexões Abertas no SQL Server no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaConexaoSQLServerHeader + @EmptyBodyEmail + @AlertaConexaoSQLServerTable + @EmptyBodyEmail+
@AlertaConexaoProcessosExecHeader + @EmptyBodyEmail + @AlertaConexaoProcessosExecTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - CLEAR
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 0
END
END -- FIM - CLEAR
END
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_Erro_Banco_Dados]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_Erro_Banco_Dados]
GO
/*******************************************************************************************************************************
-- ALERTA: ERRO BANCO DE DADOS
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_Erro_Banco_Dados]
AS
BEGIN
SET NOCOUNT ON
-- Declara as variaveis
DECLARE @Subject VARCHAR(500), @Fl_Tipo TINYINT, @Importance AS VARCHAR(6), @EmailBody VARCHAR(MAX), @EmptyBodyEmail VARCHAR(MAX),
@AlertaPaginaCorrompidaHeader VARCHAR(MAX), @AlertaPaginaCorrompidaTable VARCHAR(MAX), @EmailDestination VARCHAR(500),
@AlertaStatusDatabasesHeader VARCHAR(MAX), @AlertaStatusDatabasesTable VARCHAR(MAX), @ProfileEmail VARCHAR(200)
-- Página Corrompida
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Página Corrompida')
/*******************************************************************************************************************************
-- ALERTA: PAGINA CORROMPIDA
*******************************************************************************************************************************/
IF(OBJECT_ID('temp..#temp_Corrupcao_Pagina') IS NOT NULL) DROP TABLE #temp_Corrupcao_Pagina
SELECT SP.*
INTO #temp_Corrupcao_Pagina
FROM [msdb].[dbo].[suspect_pages] SP
LEFT JOIN [dbo].[Historico_Suspect_Pages] HSP ON SP.database_id = HSP.database_id AND SP.file_id = HSP.file_id
AND SP.[page_id] = HSP.[page_id]
AND CAST(SP.last_update_date AS DATE) = CAST(HSP.Dt_Corrupcao AS DATE)
WHERE HSP.[page_id] IS NULL
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
-- Status Database
SELECT @Id_Alerta_Parametro = 8 -- SELECT * FROM [Traces].[dbo].Alerta_Parametro
SELECT @EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Página Corrompida
/*******************************************************************************************************************************
-- Verifica se existe alguma Página Corrompida
*******************************************************************************************************************************/
IF EXISTS (SELECT TOP 1 page_id FROM #temp_Corrupcao_Pagina)
BEGIN -- INICIO - ALERTA
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaPaginaCorrompidaHeader = ''
SET @AlertaPaginaCorrompidaHeader = @AlertaPaginaCorrompidaHeader + ' Páginas Corrompidas '
SET @AlertaPaginaCorrompidaHeader = @AlertaPaginaCorrompidaHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaPaginaCorrompidaTable = CAST( (
SELECT td = Nm_Database + ' | '
+ '' + file_id + ' | '
+ '' + page_id + ' | '
+ '' + event_type + ' | '
+ '' + error_count + ' | '
+ '' + last_update_date + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT B.name AS Nm_Database,
CAST(file_id AS VARCHAR) AS file_id,
CAST(page_id AS VARCHAR) AS page_id,
CAST(event_type AS VARCHAR) AS event_type,
CAST(error_count AS VARCHAR) AS error_count,
CONVERT(VARCHAR(20), last_update_date, 120) AS last_update_date
FROM #temp_Corrupcao_Pagina A
JOIN [sys].[databases] B ON B.[database_id] = A.[database_id]
) AS D
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaPaginaCorrompidaTable = REPLACE( REPLACE( REPLACE( @AlertaPaginaCorrompidaTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaPaginaCorrompidaTable =
''
+ '
Nome Database |
File_Id |
Page_Id |
Event_Type |
Error_Count |
Last_Update_Date |
'
+ REPLACE( REPLACE( @AlertaPaginaCorrompidaTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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 alguma Página Corrompida no Banco de Dados no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaPaginaCorrompidaHeader + @EmptyBodyEmail + @AlertaPaginaCorrompidaTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@subject = @Subject,
@body = @EmailBody,
@body_format = 'HTML',
@importance = @Importance
/*******************************************************************************************************************************
-- Insere um Registro na Tabela de Controle dos Alertas
*******************************************************************************************************************************/
INSERT INTO [dbo].[Historico_Suspect_Pages]
SELECT [database_id] ,
[file_id] ,
[page_id] ,
[event_type] ,
[last_update_date]
FROM #temp_Corrupcao_Pagina
/*******************************************************************************************************************************
-- Insere um Registro na Tabela de Controle dos Alertas -> Fl_Tipo = 1 : ALERTA
*******************************************************************************************************************************/
INSERT INTO [dbo].[Alerta] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END -- FIM - ALERTA
/*******************************************************************************************************************************
-- ALERTA: DATABASE INDISPONIVEL
*******************************************************************************************************************************/
-- Status Database
SELECT @Id_Alerta_Parametro = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Status Database')
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
SELECT @EmailDestination = Ds_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Status Database
-- 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 [Id_Alerta_Parametro] = @Id_Alerta_Parametro )
/*******************************************************************************************************************************
-- Verifica se alguma Database não está ONLINE
*******************************************************************************************************************************/
IF EXISTS (
SELECT NULL
FROM [sys].[databases]
WHERE [state_desc] NOT IN ('ONLINE','RESTORING')
)
BEGIN -- INICIO - ALERTA
IF ISNULL(@Fl_Tipo, 0) = 0 -- Envia o Alerta apenas uma vez
BEGIN
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaStatusDatabasesHeader = ''
SET @AlertaStatusDatabasesHeader = @AlertaStatusDatabasesHeader + ' Status das Databases '
SET @AlertaStatusDatabasesHeader = @AlertaStatusDatabasesHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaStatusDatabasesTable = CAST( (
SELECT td = [name] + ' | '
+ '' + [state_desc] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT [name], [state_desc]
FROM [sys].[databases]
WHERE [state_desc] NOT IN ('ONLINE','RESTORING')
) AS D ORDER BY [name]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaStatusDatabasesTable = REPLACE( REPLACE( REPLACE( @AlertaStatusDatabasesTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaStatusDatabasesTable =
''
+ '
Database |
Status |
'
+ REPLACE( REPLACE( @AlertaStatusDatabasesTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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 alguma Database que não está ONLINE no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaStatusDatabasesHeader + @EmptyBodyEmail + @AlertaStatusDatabasesTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END
END -- FIM - ALERTA
ELSE
BEGIN -- INICIO - CLEAR
IF ISNULL(@Fl_Tipo, 0) = 1
BEGIN
/*******************************************************************************************************************************
-- CRIA O EMAIL - CLEAR
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaStatusDatabasesHeader = ''
SET @AlertaStatusDatabasesHeader = @AlertaStatusDatabasesHeader + ' Status das Databases '
SET @AlertaStatusDatabasesHeader = @AlertaStatusDatabasesHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- CLEAR - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaStatusDatabasesTable = CAST( (
SELECT td = [name] + ' | '
+ '' + [state_desc] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT [name], [state_desc]
FROM [sys].[databases]
) AS D ORDER BY [name]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaStatusDatabasesTable = REPLACE( REPLACE( REPLACE( @AlertaStatusDatabasesTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaStatusDatabasesTable =
''
+ '
Database |
Status |
'
+ REPLACE( REPLACE( @AlertaStatusDatabasesTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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 alguma Database que não está ONLINE no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaStatusDatabasesHeader + @EmptyBodyEmail + @AlertaStatusDatabasesTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - CLEAR
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 0
END
END -- FIM - CLEAR
END
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_Queries_Demoradas]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_Queries_Demoradas]
GO
/*******************************************************************************************************************************
-- ALERTA: QUERIES DEMORADAS
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_Queries_Demoradas]
AS
BEGIN
SET NOCOUNT ON
-- Queries Demoradas
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Queries Demoradas')
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
DECLARE @Queries_Demoradas_Parametro INT, @EmailDestination VARCHAR(500), @ProfileEmail VARCHAR(200)
SELECT @Queries_Demoradas_Parametro = Vl_Parametro, -- Quantidade
@EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Queries Demoradas
-- Cria a tabela com as queries demoradas
IF ( OBJECT_ID('tempdb..#Queries_Demoradas_Temp') IS NOT NULL )
DROP TABLE #Queries_Demoradas_Temp
SELECT [StartTime],
[DataBaseName],
[Duration],
[Reads],
[Writes],
[CPU],
[TextData]
INTO #Queries_Demoradas_Temp
FROM [dbo].[Traces]
WHERE [StartTime] >= DATEADD(mi, -5, GETDATE())
ORDER BY [Duration] DESC
-- Declara a variavel e retorna a quantidade de Queries Lentas
DECLARE @Quantidade_Queries_Demoradas INT = ( SELECT COUNT(*) FROM #Queries_Demoradas_Temp )
/*******************************************************************************************************************************
-- Verifica se existem mais de 100 Queries Lentas nos últimos 5 minutos
*******************************************************************************************************************************/
IF (@Quantidade_Queries_Demoradas > @Queries_Demoradas_Parametro)
BEGIN
-- 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
)
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - DADOS - 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
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA
*******************************************************************************************************************************/
-- Declara as variaveis
DECLARE @Importance AS VARCHAR(6), @EmailBody VARCHAR(MAX), @AlertaQueriesLentasHeader VARCHAR(MAX),
@ResultadoWhoisactiveHeader VARCHAR(MAX), @ResultadoWhoisactiveTable VARCHAR(MAX),
@AlertaQueriesLentasTable VARCHAR(MAX), @EmptyBodyEmail VARCHAR(MAX), @Subject VARCHAR(500)
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveHeader = ''
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ' TOP 50 - Processos executando no Banco de Dados '
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY - WHOISACTIVE
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveTable = 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
) AS D ORDER BY [start_time]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @ResultadoWhoisactiveTable = REPLACE( REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @ResultadoWhoisactiveTable =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaQueriesLentasHeader = ''
SET @AlertaQueriesLentasHeader = @AlertaQueriesLentasHeader + ' TOP 50 - Queries Demoradas '
SET @AlertaQueriesLentasHeader = @AlertaQueriesLentasHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaQueriesLentasTable = CAST( (
SELECT td = [StartTime] + ' | '
+ '' + [DataBaseName] + ' | '
+ '' + [Duration] + ' | '
+ '' + [Reads] + ' | '
+ '' + [Writes] + ' | '
+ '' + [CPU] + ' | '
+ '' + [TextData] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT TOP 50
CONVERT(VARCHAR(20), [StartTime], 120) AS [StartTime],
[DataBaseName],
CAST([Duration] AS VARCHAR) AS [Duration],
CAST([Reads] AS VARCHAR) AS [Reads],
CAST([Writes] AS VARCHAR) AS [Writes],
CAST([CPU] AS VARCHAR) AS [CPU],
SUBSTRING([TextData], 1, 150) AS [TextData]
FROM #Queries_Demoradas_Temp
ORDER BY [Duration] DESC
) AS D ORDER BY CAST([Duration] AS NUMERIC(15,2)) DESC
FOR XML PATH( 'tr' )) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaQueriesLentasTable = REPLACE( REPLACE( REPLACE( @AlertaQueriesLentasTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaQueriesLentasTable =
''
+ '
StartTime |
DataBaseName |
Duration |
Reads |
Writes |
CPU |
TextData (150 caracteres iniciais) |
'
+ REPLACE( REPLACE( @AlertaQueriesLentasTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- Insere um Espaço em Branco no EMAIL
--------------------------------------------------------------------------------------------------------------------------------
SET @EmptyBodyEmail = ''
SET @EmptyBodyEmail =
'' +
'
|
'
+ REPLACE( REPLACE( ISNULL(@EmptyBodyEmail,''), '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- Insere um Espaço em Branco no EMAIL
--------------------------------------------------------------------------------------------------------------------------------
SET @EmptyBodyEmail = ''
SET @EmptyBodyEmail =
'' +
'
|
'
+ REPLACE( REPLACE( ISNULL(@EmptyBodyEmail,''), '<', '<'), '>', '>')
+ ' '
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
SELECT @Importance = 'High',
@Subject = 'ALERTA: Existem ' + CAST(@Quantidade_Queries_Demoradas AS VARCHAR) + ' queries demoradas nos últimos 5 minutos no Servidor: ' + @@SERVERNAME,
@EmailBody = @ResultadoWhoisactiveHeader + @EmptyBodyEmail + @ResultadoWhoisactiveTable + @EmptyBodyEmail +
@AlertaQueriesLentasHeader + @EmptyBodyEmail + @AlertaQueriesLentasTable + @EmptyBodyEmail
/***********************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
***********************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@subject = @Subject,
@body = @EmailBody,
@body_format = 'HTML',
@importance = @Importance
END
END
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_Job_Falha]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_Job_Falha]
GO
/*******************************************************************************************************************************
-- ALERTA: JOB FALHA
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_Job_Falha]
AS
BEGIN
SET NOCOUNT ON
IF ( OBJECT_ID('tempdb..#Result_History_Jobs') IS NOT NULL )
DROP TABLE #Result_History_Jobs
CREATE TABLE #Result_History_Jobs (
[Cod] INT IDENTITY(1,1),
[Instance_Id] INT,
[Job_Id] VARCHAR(255),
[Job_Name] VARCHAR(255),
[Step_Id] INT,
[Step_Name] VARCHAR(255),
[SQl_Message_Id] INT,
[Sql_Severity] INT,
[SQl_Message] VARCHAR(4490),
[Run_Status] INT,
[Run_Date] VARCHAR(20),
[Run_Time] VARCHAR(20),
[Run_Duration] INT,
[Operator_Emailed] VARCHAR(100),
[Operator_NetSent] VARCHAR(100),
[Operator_Paged] VARCHAR(100),
[Retries_Attempted] INT,
[Nm_Server] VARCHAR(100)
)
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
DECLARE @JobFailed_Parametro INT, @EmailDestination VARCHAR(500), @ProfileEmail VARCHAR(200)
-- Job Falha
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Job Falha')
SELECT @JobFailed_Parametro = Vl_Parametro, -- Horas
@EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Jobs Failed
-- Declara as variaveis
DECLARE @Dt_Inicial VARCHAR (8), @Dt_Referencia DATETIME
SELECT @Dt_Referencia = GETDATE()
SELECT @Dt_Inicial = CONVERT(VARCHAR(8), (DATEADD (HOUR, -@JobFailed_Parametro, @Dt_Referencia)), 112)
INSERT INTO #Result_History_Jobs
EXEC [msdb].[dbo].[sp_help_jobhistory]
@mode = 'FULL',
@start_run_date = @Dt_Inicial
-- Busca os dados dos JOBS que Falharam
IF ( OBJECT_ID('tempdb..#Alerta_Job_Falharam') IS NOT NULL )
DROP TABLE #Alerta_Job_Falharam
SELECT TOP 50
[Nm_Server] AS [Server],
[Job_Name],
CASE WHEN [Run_Status] = 0 THEN 'Failed'
WHEN [Run_Status] = 1 THEN 'Succeeded'
WHEN [Run_Status] = 2 THEN 'Retry (step only)'
WHEN [Run_Status] = 3 THEN 'Cancelled'
WHEN [Run_Status] = 4 THEN 'In-progress message'
WHEN [Run_Status] = 5 THEN 'Unknown'
END AS [Status],
CAST( [Run_Date] + ' ' +
RIGHT('00' + SUBSTRING([Run_Time], (LEN([Run_Time])-5), 2), 2) + ':' +
RIGHT('00' + SUBSTRING([Run_Time], (LEN([Run_Time])-3), 2), 2) + ':' +
RIGHT('00' + SUBSTRING([Run_Time], (LEN([Run_Time])-1), 2), 2) AS VARCHAR
) AS [Dt_Execucao],
RIGHT('00' + SUBSTRING(CAST([Run_Duration] AS VARCHAR), (LEN([Run_Duration])-5), 2), 2) + ':' +
RIGHT('00' + SUBSTRING(CAST([Run_Duration] AS VARCHAR), (LEN([Run_Duration])-3), 2), 2) + ':' +
RIGHT('00' + SUBSTRING(CAST([Run_Duration] AS VARCHAR), (LEN([Run_Duration])-1), 2), 2) AS [Run_Duration],
CAST([SQl_Message] AS VARCHAR(3990)) AS [SQL_Message]
INTO #Alerta_Job_Falharam
FROM #Result_History_Jobs
WHERE
-- [Step_Id] = 0 AND condição para o retry
[Run_Status] <> 1 AND
CAST (
[Run_Date] + ' ' + RIGHT('00' + SUBSTRING([Run_Time],(LEN([Run_Time])-5), 2), 2) + ':' +
RIGHT('00' + SUBSTRING([Run_Time], (LEN([Run_Time])-3), 2), 2) + ':' +
RIGHT('00' + SUBSTRING([Run_Time], (LEN([Run_Time])-1), 2), 2) AS DATETIME
) >= DATEADD(HOUR, -@JobFailed_Parametro, @Dt_Referencia) AND
CAST ( [Run_Date] + ' ' + RIGHT('00' + SUBSTRING([Run_Time],(LEN([Run_Time])-5), 2), 2) + ':' +
RIGHT('00' + SUBSTRING([Run_Time],(LEN([Run_Time])-3), 2), 2) + ':' +
RIGHT('00' + SUBSTRING([Run_Time],(LEN([Run_Time])-1), 2), 2) AS DATETIME
) < @Dt_Referencia
ORDER BY [Dt_Execucao] DESC
/*******************************************************************************************************************************
-- Verifica se algum JOB Falhou
*******************************************************************************************************************************/
IF EXISTS(SELECT * FROM #Alerta_Job_Falharam)
BEGIN
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA
*******************************************************************************************************************************/
-- Declara as variaveis
DECLARE @EmailBody VARCHAR(MAX), @AlertaLogHeader VARCHAR(MAX), @AlertaJobFailed VARCHAR(MAX),
@EmptyBodyEmail VARCHAR(MAX), @Importance AS VARCHAR(6), @Subject VARCHAR(500)
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaLogHeader = ''
SET @AlertaLogHeader = @AlertaLogHeader + ' TOP 50 - Jobs que Falharam nas últimas ' + CAST((@JobFailed_Parametro) AS VARCHAR) + ' Horas '
SET @AlertaLogHeader = @AlertaLogHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaJobFailed = CAST( (
SELECT td = [Job_Name] + ' | '
+ '' + [Status] + ' | '
+ '' + [Dt_Execucao] + ' | '
+ '' + [Run_Duration] + ' | '
+ '' + [SQL_Message] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT [Job_Name],
[Status],
[Dt_Execucao],
[Run_Duration],
[SQL_Message]
FROM #Alerta_Job_Falharam
) AS D ORDER BY [Dt_Execucao] DESC
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaJobFailed = REPLACE( REPLACE( REPLACE( @AlertaJobFailed, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaJobFailed =
''
+ '
Nome do JOB |
Status |
Data da Execução |
Duração |
Mensagem Erro |
'
+ REPLACE( REPLACE( @AlertaJobFailed, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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: Jobs que Falharam nas últimas ' + CAST((@JobFailed_Parametro) AS VARCHAR) + ' Horas no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaLogHeader + @EmptyBodyEmail + @AlertaJobFailed + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END
END
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_SQL_Server_Reiniciado]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_SQL_Server_Reiniciado]
GO
/*******************************************************************************************************************************
-- ALERTA: SQL Server Reiniciado
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_SQL_Server_Reiniciado]
AS
BEGIN
SET NOCOUNT ON
-- SQL Server Reiniciado
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'SQL Server Reiniciado')
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
DECLARE @SQL_Reiniciado_Parametro INT, @EmailDestination VARCHAR(500), @ProfileEmail VARCHAR(200)
SELECT @SQL_Reiniciado_Parametro = Vl_Parametro, -- Minutos
@EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- SQL Server Reiniciado
-- Verifica se o SQL Server foi Reiniciado
IF ( OBJECT_ID('tempdb..#Alerta_SQL_Reiniciado') IS NOT NULL )
DROP TABLE #Alerta_SQL_Reiniciado
SELECT [create_date]
INTO #Alerta_SQL_Reiniciado
FROM [sys].[databases] WITH(NOLOCK)
WHERE [database_id] = 2 -- Verifica a Database "TempDb"
AND [create_date] >= DATEADD(MINUTE, -@SQL_Reiniciado_Parametro, GETDATE())
/*******************************************************************************************************************************
-- Verifica se o SQL foi Reiniciado
*******************************************************************************************************************************/
IF EXISTS( SELECT * FROM #Alerta_SQL_Reiniciado )
BEGIN
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA
*******************************************************************************************************************************/
-- Declara as variaveis
DECLARE @EmailBody VARCHAR(MAX), @AlertaLogHeader VARCHAR(MAX), @AlertaSQLReiniciado VARCHAR(MAX),
@EmptyBodyEmail VARCHAR(MAX), @Importance AS VARCHAR(6), @Subject VARCHAR(500)
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaLogHeader = ''
SET @AlertaLogHeader = @AlertaLogHeader + ' SQL Server Reiniciado nos últimos ' + CAST((@SQL_Reiniciado_Parametro) AS VARCHAR) + ' Minutos '
SET @AlertaLogHeader = @AlertaLogHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaSQLReiniciado = CAST( (
SELECT td = [Create_Date] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT CONVERT(VARCHAR(20), [create_date], 120) AS [Create_Date]
FROM #Alerta_SQL_Reiniciado
) AS D
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaSQLReiniciado = REPLACE( REPLACE( REPLACE( @AlertaSQLReiniciado, '<', '<' ), '>', '>' ), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaSQLReiniciado =
''
+ '
Horário Restart |
'
+ REPLACE( REPLACE( @AlertaSQLReiniciado, '<', '<' ), '>', '>' )
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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: SQL Server Reiniciado nos últimos ' + CAST((@SQL_Reiniciado_Parametro) AS VARCHAR) + ' Minutos no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaLogHeader + @EmptyBodyEmail + @AlertaSQLReiniciado + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END
END
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_Database_Criada]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_Database_Criada]
GO
/*******************************************************************************************************************************
-- ALERTA: Database Criada
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_Database_Criada]
AS
BEGIN
SET NOCOUNT ON
-- Database Criada
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Database Criada')
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
DECLARE @Database_Criada_Parametro INT, @EmailDestination VARCHAR(500), @ProfileEmail VARCHAR(200)
SELECT @Database_Criada_Parametro = Vl_Parametro, -- Horas
@EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Database Criada
-- Verifica se alguma base foi criada no dia anterior
IF ( OBJECT_ID('tempdb..#Alerta_Base_Criada') IS NOT NULL )
DROP TABLE #Alerta_Base_Criada
SELECT [name], [recovery_model_desc], [create_date]
INTO #Alerta_Base_Criada
FROM [sys].[databases] WITH(NOLOCK)
WHERE [database_id] <> 2 -- Desconsidera a Database "TempDb"
AND [create_date] >= DATEADD(HOUR, -@Database_Criada_Parametro, GETDATE())
/*******************************************************************************************************************************
-- Verifica se alguam base foi criada
*******************************************************************************************************************************/
IF EXISTS( SELECT * FROM #Alerta_Base_Criada )
BEGIN
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA
*******************************************************************************************************************************/
-- Declara as variaveis
DECLARE @EmailBody VARCHAR(MAX), @AlertaLogHeader VARCHAR(MAX), @AlertaBaseCriada VARCHAR(MAX),
@EmptyBodyEmail VARCHAR(MAX), @Importance AS VARCHAR(6), @Subject VARCHAR(500)
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaLogHeader = ''
SET @AlertaLogHeader = @AlertaLogHeader + ' Database Criada nas últimas ' + CAST((@Database_Criada_Parametro) AS VARCHAR) + ' Horas '
SET @AlertaLogHeader = @AlertaLogHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaBaseCriada = CAST( (
SELECT td =
[name] + ' | '
+ '' + [recovery_model_desc] + ' | '
+ '' + [Create_Date] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT [name], [recovery_model_desc], CONVERT(VARCHAR(20), [create_date], 120) AS [Create_Date]
FROM #Alerta_Base_Criada
) AS D
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaBaseCriada = REPLACE( REPLACE( REPLACE( @AlertaBaseCriada, '<', '<' ), '>', '>' ), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaBaseCriada =
''
+ '
Nome |
Recovery Model |
Data Criação |
'
+ REPLACE( REPLACE( @AlertaBaseCriada, '<', '<' ), '>', '>' )
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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: Database Criada nas últimas ' + CAST((@Database_Criada_Parametro) AS VARCHAR) + ' Horas no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaLogHeader + @EmptyBodyEmail + @AlertaBaseCriada + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END
END
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_Database_Sem_Backup]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_Database_Sem_Backup]
GO
/*******************************************************************************************************************************
-- ALERTA: DATABASE SEM BACKUP
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_Database_Sem_Backup]
AS
BEGIN
SET NOCOUNT ON
-- Databases sem Backup
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Database sem Backup')
-- Declara as variaveis
DECLARE @Qtd_Databases_Total INT, @Qtd_Databases_Restore INT
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
DECLARE @Database_Sem_Backup_Parametro INT, @EmailDestination VARCHAR(500), @ProfileEmail VARCHAR(200)
SELECT @Database_Sem_Backup_Parametro = Vl_Parametro, -- Horas
@EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Databases sem Backup
-- Verifica a Quantidade Total de Databases
IF ( OBJECT_ID('tempdb..#alerta_backup_databases_todas') IS NOT NULL )
DROP TABLE #alerta_backup_databases_todas
SELECT [name] AS [Nm_Database]
INTO #alerta_backup_databases_todas
FROM [sys].[databases]
WHERE [name] NOT IN ('tempdb', 'ReportServerTempDB') AND state_desc <> 'OFFLINE'
SELECT @Qtd_Databases_Total = COUNT(*)
FROM #alerta_backup_databases_todas
-- Verifica a Quantidade de Databases que tiveram Backup nas ultimas 14 horas
IF ( OBJECT_ID('tempdb..#alerta_backup_databases_com_backup') IS NOT NULL)
DROP TABLE #alerta_backup_databases_com_backup
SELECT DISTINCT [database_name] AS [Nm_Database]
INTO #alerta_backup_databases_com_backup
FROM [msdb].[dbo].[backupset] B
JOIN [msdb].[dbo].[backupmediafamily] BF ON B.[media_set_id] = BF.[media_set_id]
WHERE [backup_start_date] >= DATEADD(hh, -@Database_Sem_Backup_Parametro, GETDATE())
AND [type] IN ('D','I')
SELECT @Qtd_Databases_Restore = COUNT(*)
FROM #alerta_backup_databases_com_backup
/*******************************************************************************************************************************
-- Verifica se menos de 70 % das databases tiveram Backup
*******************************************************************************************************************************/
if(@Qtd_Databases_Restore < @Qtd_Databases_Total * 0.7)
BEGIN
-- Databases que não tiveram Backup
IF ( OBJECT_ID('tempdb..#alerta_backup_databases_sem_backup') IS NOT NULL )
DROP TABLE #alerta_backup_databases_sem_backup
SELECT A.[Nm_Database]
INTO #alerta_backup_databases_sem_backup
FROM #alerta_backup_databases_todas A WITH(NOLOCK)
LEFT JOIN #alerta_backup_databases_com_backup B WITH(NOLOCK) ON A.[Nm_Database] = B.[Nm_Database]
WHERE B.[Nm_Database] IS NULL
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA
*******************************************************************************************************************************/
-- Declara as variaveis
DECLARE @EmailBody VARCHAR(MAX), @AlertaLogHeader VARCHAR(MAX), @AlertaLogTable VARCHAR(MAX), @EmptyBodyEmail VARCHAR(MAX),
@Importance AS VARCHAR(6), @Subject VARCHAR(500)
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaLogHeader = ''
SET @AlertaLogHeader = @AlertaLogHeader + ' Databases sem Backup nas últimas ' + CAST((@Database_Sem_Backup_Parametro) AS VARCHAR) + ' Horas '
SET @AlertaLogHeader = @AlertaLogHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaLogTable = CAST( (
SELECT td = [Nm_Database] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT [Nm_Database]
FROM #alerta_backup_databases_sem_backup
) AS D ORDER BY [Nm_Database] DESC
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaLogTable = REPLACE( REPLACE( REPLACE( @AlertaLogTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaLogTable =
''
+ '
Database |
'
+ REPLACE( REPLACE( @AlertaLogTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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: Existem Databases sem Backup nas últimas ' + CAST((@Database_Sem_Backup_Parametro) AS VARCHAR) + ' Horas no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaLogHeader + @EmptyBodyEmail + @AlertaLogTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
*******************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END
END
GO
IF ( OBJECT_ID('[dbo].[stpCHECKDB_Databases]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpCHECKDB_Databases]
GO
/*******************************************************************************************************************************
-- ALERTA: CHECKDB DATABASES
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpCHECKDB_Databases]
AS
BEGIN
SET NOCOUNT ON
-- Declara a tabela que irá armazenar o nome das Databases
DECLARE @Databases TABLE (
[Id_Database] INT IDENTITY(1, 1),
[Nm_Database] VARCHAR(50)
)
-- Declara as variaveis
DECLARE @Total INT, @Loop INT, @Nm_Database VARCHAR(50)
-- Busca o nome das Databases
INSERT INTO @Databases( [Nm_Database] )
SELECT [name]
FROM [master].[sys].[databases]
WHERE [name] NOT IN ('tempdb') -- Colocar o nome da Database aqui, caso deseje desconsiderar alguma
AND [state_desc] = 'ONLINE'
-- Quantidade Total de Databases (utilizado no Loop abaixo)
SELECT @Total = MAX([Id_Database])
FROM @Databases
SET @Loop = 1
-- Realiza o CHECKDB para cada Database
WHILE ( @Loop <= @Total )
BEGIN
SELECT @Nm_Database = [Nm_Database]
FROM @Databases
WHERE [Id_Database] = @Loop
DBCC CHECKDB(@Nm_Database) WITH NO_INFOMSGS
SET @Loop = @Loop + 1
END
END
GO
IF ( OBJECT_ID('[dbo].[stpAlerta_CheckDB]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpAlerta_CheckDB]
GO
/*******************************************************************************************************************************
-- ALERTA: BANCO DE DADOS CORROMPIDO
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_CheckDB]
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT MDY
IF ( OBJECT_ID('tempdb..#TempLog') IS NOT NULL )
DROP TABLE #TempLog
CREATE TABLE #TempLog (
[LogDate] DATETIME,
[ProcessInfo] NVARCHAR(50),
[Text] NVARCHAR(MAX)
)
IF ( OBJECT_ID('tempdb..#logF') IS NOT NULL )
DROP TABLE #logF
CREATE TABLE #logF (
ArchiveNumber INT,
LogDate DATETIME,
LogSize INT
)
-- Seleciona o número de arquivos.
INSERT INTO #logF
EXEC sp_enumerrorlogs
DELETE FROM #logF
WHERE LogDate < GETDATE()-2
DECLARE @TSQL NVARCHAR(2000), @lC INT
SELECT @lC = MIN(ArchiveNumber) FROM #logF
--Loop para realizar a leitura de todo o log
WHILE @lC IS NOT NULL
BEGIN
INSERT INTO #TempLog
EXEC sp_readerrorlog @lC
SELECT @lC = MIN(ArchiveNumber)
FROM #logF
WHERE ArchiveNumber > @lC
END
IF OBJECT_ID('_Result_Corrupcao') IS NOT NULL
DROP TABLE _Result_Corrupcao
SELECT LogDate,
SUBSTRING(Text, 15, CHARINDEX(')', Text, 15) - 15) AS Nm_Database,
SUBSTRING(Text,charindex('found',Text),(charindex('Elapsed time',Text)-charindex('found',Text))) AS Erros,
Text
INTO _Result_Corrupcao
FROM #TempLog
WHERE LogDate >= GETDATE() - 1
and Text like '%DBCC CHECKDB (%'
and Text not like '%IDR%'
and substring(Text,charindex('found',Text), charindex('Elapsed time',Text) - charindex('found',Text)) <> 'found 0 errors and repaired 0 errors.'
-- Declara as variaveis
DECLARE @Subject VARCHAR(500), @Importance AS VARCHAR(6), @EmailBody VARCHAR(MAX), @EmptyBodyEmail VARCHAR(MAX),
@AlertaBancoCorrompidoHeader VARCHAR(MAX), @AlertaBancoCorrompidoTable VARCHAR(MAX), @EmailDestination VARCHAR(500), @ProfileEmail VARCHAR(200)
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
-- Banco de Dados Corrompido
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Banco de Dados Corrompido')
SELECT @EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Banco de Dados Corrompido
/*******************************************************************************************************************************
-- Verifica se existe algum Banco de Dados Corrompido
*******************************************************************************************************************************/
IF EXISTS (SELECT NULL FROM [Traces].[dbo].[_Result_Corrupcao])
BEGIN -- INICIO - ALERTA
/*******************************************************************************************************************************
-- CRIA O EMAIL - ALERTA
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaBancoCorrompidoHeader = ''
SET @AlertaBancoCorrompidoHeader = @AlertaBancoCorrompidoHeader + ' Banco de Dados Corrompido '
SET @AlertaBancoCorrompidoHeader = @AlertaBancoCorrompidoHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- ALERTA - BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @AlertaBancoCorrompidoTable = CAST( (
SELECT td = [LogDate] + ' | '
+ '' + [Nm_Database] + ' | '
+ '' + [Erros] + ' | '
+ '' + [Text] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT CONVERT(VARCHAR(20), [LogDate], 120) AS [LogDate],
[Nm_Database],
[Erros],
[Text]
FROM [Traces].[dbo].[_Result_Corrupcao]
) AS D
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @AlertaBancoCorrompidoTable = REPLACE( REPLACE( REPLACE( @AlertaBancoCorrompidoTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @AlertaBancoCorrompidoTable =
''
+ '
Data Log |
Nome Database |
Erros |
Descrição |
'
+ REPLACE( REPLACE( @AlertaBancoCorrompidoTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- 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 algum Banco de Dados Corrompido no Servidor: ' + @@SERVERNAME + '. Verifique com urgência!',
@EmailBody = @AlertaBancoCorrompidoHeader + @EmptyBodyEmail + @AlertaBancoCorrompidoTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- ENVIA O EMAIL - ALERTA
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination,
@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] ( [Id_Alerta_Parametro], [Ds_Mensagem], [Fl_Tipo] )
SELECT @Id_Alerta_Parametro, @Subject, 1
END -- FIM - ALERTA
IF ( OBJECT_ID('_Result_Corrupcao') IS NOT NULL )
DROP TABLE _Result_Corrupcao
END
GO
IF ( OBJECT_ID('[dbo].[stpEnvia_Email_Processos_Execucao]') IS NOT NULL )
DROP PROCEDURE [dbo].[stpEnvia_Email_Processos_Execucao]
GO
/*******************************************************************************************************************************
-- PROCEDURE ENVIA EMAIL WHOISACTIVE DBA
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpEnvia_Email_Processos_Execucao]
AS
BEGIN
SET NOCOUNT ON
-- Declara as variaveis
DECLARE @Subject VARCHAR(500), @Importance AS VARCHAR(6), @EmailBody VARCHAR(MAX), @EmptyBodyEmail VARCHAR(MAX),
@ResultadoWhoisactiveHeader VARCHAR(MAX), @ResultadoWhoisactiveTable VARCHAR(MAX), @EmailDestination VARCHAR(500), @ProfileEmail VARCHAR(200)
-- 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
)
-- 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
--------------------------------------------------------------------------------------------------------------------------------
-- Recupera os parametros do Alerta
--------------------------------------------------------------------------------------------------------------------------------
-- Processos em Execução
DECLARE @Id_Alerta_Parametro INT = (SELECT Id_Alerta_Parametro FROM [Traces].[dbo].Alerta_Parametro (NOLOCK) WHERE Nm_Alerta = 'Processos em Execução')
SELECT @EmailDestination = Ds_Email,
@ProfileEmail = Ds_Profile_Email
FROM [dbo].[Alerta_Parametro]
WHERE [Id_Alerta_Parametro] = @Id_Alerta_Parametro -- Processos em Execução
/*******************************************************************************************************************************
-- CRIA O EMAIL
*******************************************************************************************************************************/
--------------------------------------------------------------------------------------------------------------------------------
-- HEADER
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveHeader = ''
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ' Processos em Execução no Banco de Dados '
SET @ResultadoWhoisactiveHeader = @ResultadoWhoisactiveHeader + ''
--------------------------------------------------------------------------------------------------------------------------------
-- BODY
--------------------------------------------------------------------------------------------------------------------------------
SET @ResultadoWhoisactiveTable = 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 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
) AS D ORDER BY [start_time]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @ResultadoWhoisactiveTable = REPLACE( REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @ResultadoWhoisactiveTable =
''
+ '
[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 |
'
+ REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>')
+ ' '
--------------------------------------------------------------------------------------------------------------------------------
-- Insere um Espaço em Branco no EMAIL
--------------------------------------------------------------------------------------------------------------------------------
SET @EmptyBodyEmail = ''
SET @EmptyBodyEmail =
'' +
'
|
'
+ REPLACE( REPLACE( ISNULL(@EmptyBodyEmail,''), '<', '<'), '>', '>')
+ ' '
/*******************************************************************************************************************************
-- Seta as variavis do Email
*******************************************************************************************************************************/
SELECT @Importance = 'High',
@Subject = 'Processos em execução no Servidor: ' + @@SERVERNAME,
@EmailBody = @ResultadoWhoisactiveHeader + @EmptyBodyEmail + @ResultadoWhoisactiveTable + @EmptyBodyEmail
/***********************************************************************************************************************************
-- Inclui uma imagem com link para o site do Fabricio Lima
***********************************************************************************************************************************/
select @EmailBody = @EmailBody + '
' +
'
'
/*******************************************************************************************************************************
-- ENVIA O EMAIL
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = @ProfileEmail,
@recipients = @EmailDestination ,
@subject = @Subject,
@body = @EmailBody,
@body_format = 'HTML',
@importance = @Importance
END |