WordPress.com

Create PROCEDURE [dbo].[P_Envia_CheckList_Diario_DBA]@Nm_Empresa VARCHAR(100),@Profile_Email VARCHAR(100),@Ds_Email VARCHAR(MAX)ASBEGIN/***********************************************************************************************************************************--Valida??o nos Par?metros de Entrada***********************************************************************************************************************************/IF (@Nm_Empresa IS NULL OR @Nm_Empresa = '')BEGINRAISERROR('Favor informar o Nome da Empresa!',16,1)RETURNENDELSE IF (@Profile_Email IS NULL OR @Profile_Email = '')BEGINRAISERROR('Favor informar o Profile de E-mail!',16,1)RETURNENDELSE IF (@Ds_Email IS NULL OR @Ds_Email = '')BEGINRAISERROR('Favor informar os Destinatários de E-mail!',16,1)RETURNEND/***********************************************************************************************************************************--Disponibilidade SQL Server - HEADER***********************************************************************************************************************************/DECLARE @DisponibilidadeSQL_Header VARCHAR(MAX)SET @DisponibilidadeSQL_Header = ''SET @DisponibilidadeSQL_Header = @DisponibilidadeSQL_Header + ' Tempo de Disponibilidade do SQL Server ' SET @DisponibilidadeSQL_Header = @DisponibilidadeSQL_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Disponibilidade SQL Server - BODY------------------------------------------------------------------------------------------------------------------------------------DECLARE @DisponibilidadeSQL_Table VARCHAR(MAX)SET @DisponibilidadeSQL_Table = CAST( ( SELECT td =DisponibilidadeSQL + '</td>'FROM ( SELECTCASE WHEN(RTRIM(CONVERT(CHAR(17), DATEDIFF(SECOND, CONVERT(DATETIME, [Create_Date]), GETDATE()) / 86400)) = 0) OR(RTRIM(CONVERT(CHAR(17), DATEDIFF(SECOND, CONVERT(DATETIME, [Create_Date]), GETDATE()) / 86400)) > 365)THEN ' bgcolor=yellow>' ELSE '' END + RTRIM(CONVERT(CHAR(17), DATEDIFF(SECOND, CONVERT(DATETIME, [Create_Date]), GETDATE()) / 86400)) + ' Dia(s) ' +RIGHT('00' + RTRIM(CONVERT(CHAR(7), DATEDIFF(SECOND, CONVERT(DATETIME, [Create_Date]), GETDATE()) % 86400 / 3600)), 2) + ' Hora(s) ' +RIGHT('00' + RTRIM(CONVERT(CHAR(7), DATEDIFF(SECOND, CONVERT(DATETIME, [Create_Date]), GETDATE()) % 86400 % 3600 / 60)), 2) + ' Minuto(s) 'AS DisponibilidadeSQLFROM [sys].[databases]WHERE [Database_Id] = 2 ) AS DFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX)) SET @DisponibilidadeSQL_Table =REPLACE( REPLACE( REPLACE( REPLACE(@DisponibilidadeSQL_Table, '<', '<'), '>', '>'), '<td> ', '<td align=center '),'<td>', '<td align=center>') SET @DisponibilidadeSQL_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="300" bgcolor=#0B0B61>Tempo Disponibilidade</th></tr>' + REPLACE( REPLACE( @DisponibilidadeSQL_Table, '<', '<'), '>', '>')+ '</table>'/***********************************************************************************************************************************--Espa?o em Disco - Header***********************************************************************************************************************************/DECLARE @EspacoDisco_Header VARCHAR(MAX)SET @EspacoDisco_Header = ''SET @EspacoDisco_Header = @EspacoDisco_Header + ' Espa?o em Disco ' SET @EspacoDisco_Header = @EspacoDisco_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Espa?o em Disco - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @EspacoDisco_Table VARCHAR(MAX)SET @EspacoDisco_Table = CAST( ( SELECT td = CASE WHEN [SpaceUsed_Percent] = '-' THEN '' WHEN CAST([SpaceUsed_Percent] AS NUMERIC(9,2)) >= 90 THEN ' bgcolor=yellow>' ELSE '' END + [DriveName]+ '</td>' ++ '<td>' + CASE WHEN [SpaceUsed_Percent] = '-' THEN '' WHEN CAST([SpaceUsed_Percent] AS NUMERIC(9,2)) >= 90 THEN ' bgcolor=yellow>' ELSE '' END + [TotalSize_GB]+ '</td>' ++ '<td>' + CASE WHEN [SpaceUsed_Percent] = '-' THEN '' WHEN CAST([SpaceUsed_Percent] AS NUMERIC(9,2)) >= 90 THEN ' bgcolor=yellow>' ELSE '' END + [SpaceUsed_GB]+ '</td>' ++ '<td>' + CASE WHEN [SpaceUsed_Percent] = '-' THEN '' WHEN CAST([SpaceUsed_Percent] AS NUMERIC(9,2)) >= 90 THEN ' bgcolor=yellow>' ELSE '' END + [FreeSpace_GB]+ '</td>' ++ '<td>' + CASE WHEN [SpaceUsed_Percent] = '-' THEN '' WHEN CAST([SpaceUsed_Percent] AS NUMERIC(9,2)) >= 90 THEN ' bgcolor=yellow>' ELSE '' END + [SpaceUsed_Percent]+ '</td>'FROM ( SELECT[DriveName], ISNULL(CAST([TotalSize_GB]AS VARCHAR), '-')AS [TotalSize_GB], ISNULL(CAST([SpaceUsed_GB]AS VARCHAR), '-')AS [SpaceUsed_GB],ISNULL(CAST([FreeSpace_GB]AS VARCHAR), '-')AS [FreeSpace_GB], ISNULL(CAST([SpaceUsed_Percent] AS VARCHAR), '-')AS [SpaceUsed_Percent] FROM [dbo].[CheckList_Espaco_Disco] ) AS D ORDER BY [DriveName]FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX)) SET @EspacoDisco_Table =REPLACE( REPLACE( REPLACE( REPLACE(@EspacoDisco_Table, '<', '<'), '>', '>'), '<td> ', '<td align=center '),'<td>', '<td align=center>') SET @EspacoDisco_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="100" bgcolor=#0B0B61>Drive</th><th width="150" bgcolor=#0B0B61>Tamanho (MB)</th><th width="150" bgcolor=#0B0B61>Utilizado (MB)</th><th width="150" bgcolor=#0B0B61>Livre (MB)</th><th width="150" bgcolor=#0B0B61>Utilizado (%)</th></tr>' + REPLACE( REPLACE( @EspacoDisco_Table, '<', '<'), '>', '>')+ '</table>' /***********************************************************************************************************************************--Arquivos Dados - Header***********************************************************************************************************************************/DECLARE @ArquivosDados_Header VARCHAR(MAX)SET @ArquivosDados_Header = ''SET @ArquivosDados_Header = @ArquivosDados_Header + ' TOP 5 - Informa??es dos Arquivos de Dados (MDF e NDF) ' SET @ArquivosDados_Header = @ArquivosDados_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Arquivos Dados - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @ArquivosDados_Table VARCHAR(MAX)SET @ArquivosDados_Table = CAST( (SELECT td = [Nm_Database]+ '</td><td>' + [Logical_Name]+ '</td><td>' + [Total_Reservado]+ '</td><td>' + [Total_Utilizado]+ '</td><td>' + [Espaco_Livre (MB)]+ '</td><td>' + [Espaco_Livre (%)]+ '</td><td>' + [MAXSIZE]+ '</td><td>' + [Growth]+'</td>' FROM ( SELECTTOP 5[Nm_Database], ISNULL([Logical_Name], '-')AS [Logical_Name], ISNULL(CAST([Total_Reservado]AS VARCHAR), '-')AS [Total_Reservado], ISNULL(CAST([Total_Utilizado]AS VARCHAR), '-')AS [Total_Utilizado],ISNULL(CAST([Espaco_Livre (MB)]AS VARCHAR), '-')AS [Espaco_Livre (MB)], ISNULL(CAST([Espaco_Livre (%)]AS VARCHAR), '-')AS [Espaco_Livre (%)],ISNULL(CAST([MaxSize]AS VARCHAR), '-')AS [MAXSIZE], ISNULL(CAST([Growth]AS VARCHAR), '-')AS [Growth]FROM [dbo].[CheckList_Arquivos_Dados]ORDER BYCAST(REPLACE([Total_Reservado], '-', 0) AS NUMERIC(15,2)) DESC,CAST(REPLACE([Total_Utilizado], '-', 0) AS NUMERIC(15,2)) DESC ) AS D ORDER BYCAST(REPLACE([Total_Reservado], '-', 0) AS NUMERIC(15,2)) DESC,CAST(REPLACE([Total_Utilizado], '-', 0) AS NUMERIC(15,2)) DESC FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @ArquivosDados_Table = REPLACE( REPLACE( REPLACE(@ArquivosDados_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @ArquivosDados_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="250" bgcolor=#0B0B61>Nome Database</th><th width="250" bgcolor=#0B0B61>Nome Lógico</th><th width="200" bgcolor=#0B0B61>Total Reservado (MB)</th><th width="200" bgcolor=#0B0B61>Total Utilizado (MB)</th><th width="150" bgcolor=#0B0B61>Espaco_Livre (MB)</th><th width="150" bgcolor=#0B0B61>Espaco_Livre (%)</th><th width="150" bgcolor=#0B0B61>MAXSIZE</th><th width="150" bgcolor=#0B0B61>Growth</th> </tr>' + REPLACE( REPLACE( @ArquivosDados_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Arquivos Log - Header***********************************************************************************************************************************/DECLARE @ArquivosLog_Header VARCHAR(MAX)SET @ArquivosLog_Header = ''SET @ArquivosLog_Header = @ArquivosLog_Header + ' TOP 5 - Informa??es dos Arquivos de Log (LDF) ' SET @ArquivosLog_Header = @ArquivosLog_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Arquivos Log - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @ArquivosLog_Table VARCHAR(MAX)SET @ArquivosLog_Table = CAST( (SELECT td = [Nm_Database]+ '</td><td>' + [Logical_Name]+ '</td><td>' + [Total_Reservado]+ '</td><td>' + [Total_Utilizado]+ '</td><td>' + [Espaco_Livre (MB)]+ '</td><td>' + [Espaco_Livre (%)]+ '</td><td>' + [MAXSIZE]+ '</td><td>' + [Growth]+'</td>' FROM ( SELECTTOP 5[Nm_Database], ISNULL([Logical_Name], '-')AS [Logical_Name], ISNULL(CAST([Total_Reservado]AS VARCHAR), '-')AS [Total_Reservado], ISNULL(CAST([Total_Utilizado]AS VARCHAR), '-')AS [Total_Utilizado],ISNULL(CAST([Espaco_Livre (MB)]AS VARCHAR), '-')AS [Espaco_Livre (MB)], ISNULL(CAST([Espaco_Livre (%)]AS VARCHAR), '-')AS [Espaco_Livre (%)],ISNULL(CAST([MaxSize]AS VARCHAR), '-')AS [MAXSIZE], ISNULL(CAST([Growth]AS VARCHAR), '-')AS [Growth]FROM [dbo].[CheckList_Arquivos_Log]ORDER BYCAST(REPLACE([Total_Reservado], '-', 0) AS NUMERIC(15,2)) DESC,CAST(REPLACE([Total_Utilizado], '-', 0) AS NUMERIC(15,2)) DESC ) AS D ORDER BYCAST(REPLACE([Total_Reservado], '-', 0) AS NUMERIC(15,2)) DESC,CAST(REPLACE([Total_Utilizado], '-', 0) AS NUMERIC(15,2)) DESC FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @ArquivosLog_Table = REPLACE( REPLACE( REPLACE(@ArquivosLog_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @ArquivosLog_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="250" bgcolor=#0B0B61>Nome Database</th><th width="250" bgcolor=#0B0B61>Nome Lógico</th><th width="200" bgcolor=#0B0B61>Total Reservado (MB)</th><th width="200" bgcolor=#0B0B61>Total Utilizado (MB)</th><th width="150" bgcolor=#0B0B61>Espaco_Livre (MB)</th><th width="150" bgcolor=#0B0B61>Espaco_Livre (%)</th><th width="150" bgcolor=#0B0B61>MAXSIZE</th><th width="150" bgcolor=#0B0B61>Growth</th> </tr>' + REPLACE( REPLACE( @ArquivosLog_Table, '<', '<'), '>', '>') + '</table>' /***********************************************************************************************************************************--Crescimento das Bases - Header***********************************************************************************************************************************/DECLARE @CrescimentoBases_Header VARCHAR(MAX)SET @CrescimentoBases_Header = ''SET @CrescimentoBases_Header = @CrescimentoBases_Header + ' TOP 10 - Crescimento das Bases 'SET @CrescimentoBases_Header = @CrescimentoBases_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Crescimento das Bases - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @CrescimentoBases_Table VARCHAR(MAX) SET @CrescimentoBases_Table = CAST( ( SELECT td = CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Nm_Database]+ ''ELSE [Nm_Database] END + '</td><td>' +CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Tamanho_Atual]+ ''ELSE [Tamanho_Atual] END + '</td><td>' +CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Cresc_1_dia]+ ''ELSE [Cresc_1_dia] END + '</td><td>' +CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Cresc_15_dia]+ ''ELSE [Cresc_15_dia] END + '</td><td>' +CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Cresc_30_dia]+ ''ELSE [Cresc_30_dia] END + '</td><td>' +CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Cresc_60_dia]+ ''ELSE [Cresc_60_dia] END + '</td>' FROM ( SELECTTOP 10[Nm_Servidor], [Nm_Database], ISNULL(CAST([Tamanho_Atual] AS VARCHAR), '-') AS [Tamanho_Atual],ISNULL(CAST([Cresc_1_dia] AS VARCHAR), '-') AS [Cresc_1_dia],ISNULL(CAST([Cresc_15_dia] AS VARCHAR), '-') AS [Cresc_15_dia], ISNULL(CAST([Cresc_30_dia] AS VARCHAR), '-') AS [Cresc_30_dia],ISNULL(CAST([Cresc_60_dia] AS VARCHAR), '-') AS [Cresc_60_dia]FROM [dbo].[CheckList_Database_Growth_Email]WHERE [Nm_Servidor] IS NOT NULL-- REGISTROS NORMAISUNIONSELECT[Nm_Servidor], [Nm_Database], ISNULL(CAST([Tamanho_Atual] AS VARCHAR), '-') AS [Tamanho_Atual], ISNULL(CAST([Cresc_1_dia] AS VARCHAR), '-') AS [Cresc_1_dia],ISNULL(CAST([Cresc_15_dia] AS VARCHAR), '-') AS [Cresc_15_dia], ISNULL(CAST([Cresc_30_dia] AS VARCHAR), '-') AS [Cresc_30_dia],ISNULL(CAST([Cresc_60_dia] AS VARCHAR), '-') AS [Cresc_60_dia]FROM [dbo].[CheckList_Database_Growth_Email]WHERE [Nm_Servidor] IS NULL-- TOTAL GERAL ) AS D ORDER BY[Nm_Servidor] DESC,CAST(ABS(REPLACE([Cresc_1_dia], '-', 0)) AS NUMERIC(15,2)) DESC,CAST(ABS(REPLACE([Cresc_15_dia], '-', 0)) AS NUMERIC(15,2)) DESC,CAST(ABS(REPLACE([Cresc_30_dia], '-', 0)) AS NUMERIC(15,2)) DESC,CAST(ABS(REPLACE([Cresc_60_dia], '-', 0)) AS NUMERIC(15,2)) DESCFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @CrescimentoBases_Table = REPLACE( REPLACE( REPLACE( REPLACE(@CrescimentoBases_Table, '<', '<'), '>', '>'), '<td> ', '<td align=center '),'<td>', '<td align=center>') SET @CrescimentoBases_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="250" bgcolor=#0B0B61>Nome Database</th><th width="200" bgcolor=#0B0B61>Tamanho Atual (MB)</th><th width="150" bgcolor=#0B0B61>Cresc. 1 Dia (MB)</th><th width="150" bgcolor=#0B0B61>Cresc. 15 Dia (MB)</th><th width="150" bgcolor=#0B0B61>Cresc. 30 Dia (MB)</th><th width="150" bgcolor=#0B0B61>Cresc. 60 Dia (MB)</th> </tr>' + REPLACE( REPLACE( @CrescimentoBases_Table, '<', '<'), '>', '>') + '</table>' /***********************************************************************************************************************************--Crescimento das Tabelas - Header***********************************************************************************************************************************/DECLARE @CrescimentoTabelas_Header VARCHAR(MAX)SET @CrescimentoTabelas_Header = ''SET @CrescimentoTabelas_Header = @CrescimentoTabelas_Header + ' TOP 10 - Crescimento das Tabelas ' SET @CrescimentoTabelas_Header = @CrescimentoTabelas_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Crescimento das Tabelas - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @CrescimentoTabelas_Table VARCHAR(MAX) SET @CrescimentoTabelas_Table = CAST( ( SELECT td = CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Nm_Database]+ ''ELSE [Nm_Database] END + '</td><td>' +CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Nm_Tabela]+ ''ELSE [Nm_Tabela] END + '</td><td>' +CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Tamanho_Atual]+ ''ELSE [Tamanho_Atual] END + '</td><td>' +CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Cresc_1_dia]+ ''ELSE [Cresc_1_dia] END + '</td><td>' +CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Cresc_15_dia]+ ''ELSE [Cresc_15_dia] END + '</td><td>' +CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Cresc_30_dia]+ ''ELSE [Cresc_30_dia] END + '</td><td>' +CASE WHEN [Nm_Database] = 'TOTAL GERAL'THEN ' bgcolor=#0B0B61>' + [Cresc_60_dia]+ ''ELSE [Cresc_60_dia] END + '</td>' FROM (SELECTTOP 10[Nm_Servidor], [Nm_Database], ISNULL([Nm_Tabela], '-') AS [Nm_Tabela], ISNULL(CAST([Tamanho_Atual] AS VARCHAR), '-') AS [Tamanho_Atual], ISNULL(CAST([Cresc_1_dia]AS VARCHAR), '-') AS [Cresc_1_dia],ISNULL(CAST([Cresc_15_dia]AS VARCHAR), '-') AS [Cresc_15_dia], ISNULL(CAST([Cresc_30_dia]AS VARCHAR), '-') AS [Cresc_30_dia],ISNULL(CAST([Cresc_60_dia]AS VARCHAR), '-') AS [Cresc_60_dia]FROM [dbo].[CheckList_Table_Growth_Email]WHERE [Nm_Servidor] IS NOT NULL-- REGISTROS NORMAISUNION ALLSELECT[Nm_Servidor], [Nm_Database], ISNULL([Nm_Tabela], '-') AS [Nm_Tabela], ISNULL(CAST([Tamanho_Atual] AS VARCHAR), '-') AS [Tamanho_Atual], ISNULL(CAST([Cresc_1_dia]AS VARCHAR), '-') AS [Cresc_1_dia],ISNULL(CAST([Cresc_15_dia]AS VARCHAR), '-') AS [Cresc_15_dia], ISNULL(CAST([Cresc_30_dia]AS VARCHAR), '-') AS [Cresc_30_dia],ISNULL(CAST([Cresc_60_dia]AS VARCHAR), '-') AS [Cresc_60_dia]FROM [dbo].[CheckList_Table_Growth_Email]WHERE [Nm_Servidor] IS NULL-- TOTAL GERAL ) AS D ORDER BY[Nm_Servidor] DESC,CAST(ABS(REPLACE([Cresc_1_dia], '-', 0)) AS NUMERIC(15,2)) DESC,CAST(ABS(REPLACE([Cresc_15_dia], '-', 0)) AS NUMERIC(15,2)) DESC,CAST(ABS(REPLACE([Cresc_30_dia], '-', 0)) AS NUMERIC(15,2)) DESC,CAST(ABS(REPLACE([Cresc_60_dia], '-', 0)) AS NUMERIC(15,2)) DESCFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @CrescimentoTabelas_Table = REPLACE( REPLACE( REPLACE( REPLACE(@CrescimentoTabelas_Table, '<', '<'), '>', '>'), '<td> ', '<td align=center '),'<td>', '<td align=center>') SET @CrescimentoTabelas_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="250" bgcolor=#0B0B61>Nome Database</th><th width="250" bgcolor=#0B0B61>Nome Tabela</th><th width="200" bgcolor=#0B0B61>Tamanho Atual (MB)</th><th width="150" bgcolor=#0B0B61>Cresc. 1 Dia (MB)</th><th width="150" bgcolor=#0B0B61>Cresc. 15 Dia (MB)</th><th width="150" bgcolor=#0B0B61>Cresc. 30 Dia (MB)</th><th width="150" bgcolor=#0B0B61>Cresc. 60 Dia (MB)</th></tr>' + REPLACE( REPLACE(@CrescimentoTabelas_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Utilizacao Arquivos Database - Writes - Header***********************************************************************************************************************************/DECLARE @UtilizacaoArqWrites_Header VARCHAR(MAX)SET @UtilizacaoArqWrites_Header = ''SET @UtilizacaoArqWrites_Header = @UtilizacaoArqWrites_Header + ' TOP 10 - Utiliza??o Arquivos Databases - Writes (09:00 - 18:00) 'SET @UtilizacaoArqWrites_Header = @UtilizacaoArqWrites_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Utilizacao Arquivos Database - Writes - Informa??es------------------------------------------------------------------------------------------------------------------------------------ DECLARE @UtilizacaoArqWrites_Table VARCHAR(MAX)SET @UtilizacaoArqWrites_Table = CAST( ( SELECT td = Nm_Database+ '</td><td>' + file_id+'</td><td>' + io_stall_write_ms+ '</td><td>' + num_of_writes+'</td><td>' + [avg_write_stall_ms]+'</td>' FROM ( selectNm_Database,ISNULL(CAST(file_id AS VARCHAR), '-') AS file_id,ISNULL(CAST(io_stall_write_ms AS VARCHAR), '-') AS io_stall_write_ms,ISNULL(CAST(num_of_writes AS VARCHAR), '-') AS num_of_writes,ISNULL(CAST([avg_write_stall_ms] AS VARCHAR), '-') AS [avg_write_stall_ms]from [dbo].[CheckList_Utilizacao_Arquivo_Writes] ) AS D ORDER BYCAST(CAST(REPLACE([avg_write_stall_ms], '-', 0) AS VARCHAR) AS NUMERIC(15,1)) DESC, CAST(REPLACE(num_of_writes, '-', 0) AS BIGINT) DESCFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX)) SET @UtilizacaoArqWrites_Table = REPLACE( REPLACE( REPLACE(@UtilizacaoArqWrites_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @UtilizacaoArqWrites_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="300" bgcolor=#0B0B61>Nome Database</th><th width="200" bgcolor=#0B0B61>File ID</th><th width="200" bgcolor=#0B0B61>io_stall_write_ms</th><th width="200" bgcolor=#0B0B61>num_of_writes</th><th width="200" bgcolor=#0B0B61>avg_write_stall_ms</th></tr>' + REPLACE( REPLACE(@UtilizacaoArqWrites_Table, '<', '<'), '>', '>')+ '</table>' /***********************************************************************************************************************************--Utilizacao Arquivos Database - Reads - Header***********************************************************************************************************************************/DECLARE @UtilizacaoArqReads_Header VARCHAR(MAX)SET @UtilizacaoArqReads_Header = ''SET @UtilizacaoArqReads_Header = @UtilizacaoArqReads_Header + ' TOP 10 - Utiliza??o Arquivos Databases - Reads (09:00 - 18:00) 'SET @UtilizacaoArqReads_Header = @UtilizacaoArqReads_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Utilizacao Arquivos Database - Reads - Informa??es------------------------------------------------------------------------------------------------------------------------------------ DECLARE @UtilizacaoArqReads_Table VARCHAR(MAX)SET @UtilizacaoArqReads_Table = CAST( ( SELECT td = Nm_Database+ '</td><td>' + file_id+'</td><td>' + io_stall_read_ms+ '</td><td>' + num_of_reads+'</td><td>' + [avg_read_stall_ms]+'</td>' FROM ( selectNm_Database,ISNULL(CAST(file_id AS VARCHAR), '-') AS file_id,ISNULL(CAST(io_stall_read_ms AS VARCHAR), '-') AS io_stall_read_ms,ISNULL(CAST(num_of_reads AS VARCHAR), '-') AS num_of_reads,ISNULL(CAST([avg_read_stall_ms] AS VARCHAR), '-') AS [avg_read_stall_ms]from [dbo].[CheckList_Utilizacao_Arquivo_Reads] ) AS D ORDER BYCAST(CAST(REPLACE([avg_read_stall_ms], '-', 0) AS VARCHAR) AS NUMERIC(15,1)) DESC, CAST(REPLACE(num_of_reads, '-', 0) AS BIGINT) DESCFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @UtilizacaoArqReads_Table = REPLACE( REPLACE( REPLACE(@UtilizacaoArqReads_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @UtilizacaoArqReads_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="300" bgcolor=#0B0B61>Nome Database</th><th width="200" bgcolor=#0B0B61>File ID</th><th width="200" bgcolor=#0B0B61>io_stall_read_ms</th><th width="200" bgcolor=#0B0B61>num_of_reads</th><th width="200" bgcolor=#0B0B61>avg_read_stall_ms</th></tr>' + REPLACE( REPLACE(@UtilizacaoArqReads_Table, '<', '<'), '>', '>')+ '</table>'/***********************************************************************************************************************************--Databases Sem Backup - Header***********************************************************************************************************************************/DECLARE @DatabaseSemBackup_Header VARCHAR(MAX)SET @DatabaseSemBackup_Header = ''SET @DatabaseSemBackup_Header = @DatabaseSemBackup_Header + ' TOP 10 - Databases Sem Backup nas últimas 16 Horas 'SET @DatabaseSemBackup_Header = @DatabaseSemBackup_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Databases Sem Backup - Informa??es------------------------------------------------------------------------------------------------------------------------------------ DECLARE @DatabaseSemBackup_Table VARCHAR(MAX)SET @DatabaseSemBackup_Table = CAST( ( SELECT td = [Nm_Database] + '</td>' FROM ( SELECTTOP 10 CASE WHEN[Nm_Database] <> 'Sem registro de Databases Sem Backup nas últimas 16 horas.'THEN ' bgcolor=yellow>' ELSE '' END + [Nm_Database] AS [Nm_Database]FROM [dbo].[CheckList_Databases_Sem_Backup]ORDER BY [Nm_Database] ) AS D ORDER BY [Nm_Database]FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @DatabaseSemBackup_Table =REPLACE( REPLACE( REPLACE( REPLACE(@DatabaseSemBackup_Table, '<', '<'), '>', '>'), '<td> ', '<td align=center '),'<td>', '<td align=center>')SET @DatabaseSemBackup_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="250" bgcolor=#0B0B61>Nome Database</th> </tr>' + REPLACE( REPLACE(@DatabaseSemBackup_Table, '<', '<'), '>', '>')+ '</table>' /***********************************************************************************************************************************--Backups Executados - Header***********************************************************************************************************************************/DECLARE @Backup_Header VARCHAR(MAX)SET @Backup_Header = ''SET @Backup_Header = @Backup_Header + ' TOP 10 - Backup FULL e Diferencial das Bases 'SET @Backup_Header = @Backup_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Backups Executados - Informa??es------------------------------------------------------------------------------------------------------------------------------------ DECLARE @Backup_Table VARCHAR(MAX)SET @Backup_Table = CAST( ( SELECT td = [Database_Name]+ '</td><td>' + [Backup_Start_Date]+ '</td><td>' + [Tempo_Min]+'</td><td>' + [Recovery_Model]+ '</td><td>' + [Tipo]+ '</td><td>' + [Tamanho_MB]+'</td>' FROM ( SELECTTOP 10[Database_Name], ISNULL(CONVERT(VARCHAR, [Backup_Start_Date], 120), '-') AS [Backup_Start_Date], ISNULL(CAST([Tempo_Min] AS VARCHAR), '-')AS [Tempo_Min],ISNULL(CAST([Recovery_Model] AS VARCHAR), '-')AS [Recovery_Model],ISNULL(CASE [Type]WHEN 'D' THEN 'FULL'WHEN 'I' THEN 'Diferencial'WHEN 'L' THEN 'Log'END, '-')AS [Tipo],ISNULL(CAST([Tamanho_MB] AS VARCHAR), '-')AS [Tamanho_MB]FROM [dbo].[CheckList_Backups_Executados]ORDER BY CAST(ABS(REPLACE([Tamanho_MB], '-', 0)) AS NUMERIC(15,2)) DESC ) AS D ORDER BY CAST(ABS(REPLACE([Tamanho_MB], '-', 0)) AS NUMERIC(15,2)) DESCFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @Backup_Table = REPLACE( REPLACE( REPLACE(@Backup_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @Backup_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="170" bgcolor=#0B0B61>Nome Database</th><th width="170" bgcolor=#0B0B61>Horário Execu??o</th><th width="120" bgcolor=#0B0B61>Tempo (min)</th><th width="120" bgcolor=#0B0B61>Recovery</th><th width="120" bgcolor=#0B0B61>Tipo Backup</th><th width="120" bgcolor=#0B0B61>Tamanho (MB)</th> </tr>' + REPLACE( REPLACE(@Backup_Table, '<', '<'), '>', '>')+ '</table>'/***********************************************************************************************************************************--Queries em Execu??o - Header***********************************************************************************************************************************/ DECLARE @QueriesRunning_Header VARCHAR(MAX)SET @QueriesRunning_Header = ''SET @QueriesRunning_Header = @QueriesRunning_Header + ' TOP 5 - Queries em Execu??o a mais de 2 horas ' SET @QueriesRunning_Header = @QueriesRunning_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Queries em Execu??o - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @QueriesRunning_Table VARCHAR(MAX) SET @QueriesRunning_Table = CAST( ( SELECT td = [dd hh:mm:ss.mss]+ '</td><td>' + [database_name]+ '</td><td>' + [login_name]+ '</td><td>' + [host_name]+ '</td><td>' + [start_time]+'</td><td>' + [status]+ '</td><td>' + [session_id]+'</td><td>' + [blocking_session_id]+ '</td><td>' + [wait_info]+'</td><td>' + [open_tran_count]+ '</td><td>' + [CPU]+'</td><td>' + [reads]+ '</td><td>' + [writes]+'</td><td>' + [sql_command]+ '</td>' FROM (SELECTTOP 5ISNULL([dd hh:mm:ss.mss], '-')AS [dd hh:mm:ss.mss], [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_info], ISNULL(CAST([open_tran_count] AS VARCHAR), '-')AS [open_tran_count], ISNULL(CAST([CPU] AS VARCHAR), '-')AS [CPU], ISNULL(CAST([reads] AS VARCHAR), '-')AS [reads], ISNULL(CAST([writes] AS VARCHAR), '-')AS [writes], ISNULL(SUBSTRING(CAST([sql_command] AS VARCHAR), 1, 150), '-')AS [sql_command]FROM [dbo].[CheckList_Queries_Running]ORDER BY [start_time] ) AS D ORDER BY [start_time]FOR XML PATH( 'tr' )) AS VARCHAR(MAX) ) SET @QueriesRunning_Table = REPLACE( REPLACE( REPLACE(@QueriesRunning_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @QueriesRunning_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="150" bgcolor=#0B0B61>dd hh:mm:ss.mss</th><th width="200" bgcolor=#0B0B61>Database</th> <th width="150" bgcolor=#0B0B61>Login</th><th width="150" bgcolor=#0B0B61>Host Name</th> <th width="150" bgcolor=#0B0B61>Hora Início</th><th width="100" bgcolor=#0B0B61>Status</th> <th width="100" bgcolor=#0B0B61>Session ID</th><th width="100" bgcolor=#0B0B61>Blocking Session ID</th> <th width="150" bgcolor=#0B0B61>Wait Info</th><th width="100" bgcolor=#0B0B61>Transa??es Abertas</th> <th width="100" bgcolor=#0B0B61>CPU</th><th width="100" bgcolor=#0B0B61>Reads</th> <th width="100" bgcolor=#0B0B61>Writes</th><th width="200" bgcolor=#0B0B61>Query</th> </tr>' + REPLACE( REPLACE(@QueriesRunning_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Jobs em Execu??o - Header***********************************************************************************************************************************/ DECLARE @JobsRunning_Header VARCHAR(MAX)SET @JobsRunning_Header = ''SET @JobsRunning_Header = @JobsRunning_Header + ' TOP 10 - Jobs em Execu??o ' SET @JobsRunning_Header = @JobsRunning_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Jobs em Execu??o - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @JobsRunning_Table VARCHAR(MAX) SET @JobsRunning_Table = CAST( ( SELECT td = [Nm_JOB]+ '</td><td>' + [Dt_Inicio]+ '</td><td>' + [Qt_Duracao]+ '</td><td>' + [Nm_Step]+ '</td>' FROM (SELECTTOP 10[Nm_JOB], ISNULL(CONVERT(VARCHAR(16), [Dt_Inicio],120), '-')AS [Dt_Inicio], ISNULL(Qt_Duracao, '-')AS [Qt_Duracao], ISNULL([Nm_Step], '-')AS [Nm_Step]FROM [dbo].[CheckList_Jobs_Running]ORDER BY [Dt_Inicio] ) AS D ORDER BY [Dt_Inicio]FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @JobsRunning_Table = REPLACE( REPLACE( REPLACE(@JobsRunning_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @JobsRunning_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="300" bgcolor=#0B0B61>Nome Job</th><th width="200" bgcolor=#0B0B61>Data Início</th> <th width="200" bgcolor=#0B0B61>Dura??o</th><th width="200" bgcolor=#0B0B61>Nome Step</th> </tr>' + REPLACE( REPLACE(@JobsRunning_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Jobs Alterados - Header***********************************************************************************************************************************/ DECLARE @JobsAlterados_Header VARCHAR(MAX)SET @JobsAlterados_Header = ''SET @JobsAlterados_Header = @JobsAlterados_Header + ' TOP 10 - Jobs Alterados 'SET @JobsAlterados_Header = @JobsAlterados_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Jobs Alterados - Informa??es------------------------------------------------------------------------------------------------------------------------------------ DECLARE @JobsAlterados_Table VARCHAR(MAX) SET @JobsAlterados_Table = CAST( ( SELECT td = [Nm_Job]+ '</td><td>' + [Fl_Habilitado]+ '</td><td>' + [Dt_Criacao]+ '</td><td>' + [Dt_Modificacao] + '</td><td>' + [Nr_Versao]+'</td>' FROM (SELECTTOP 10[Nm_Job], ISNULL(CASE [Fl_Habilitado] WHEN 1 THEN 'SIM' WHEN 0 THEN 'N?O' END, '-')AS [Fl_Habilitado], ISNULL(CONVERT(VARCHAR, [Dt_Criacao], 120), '-')AS [Dt_Criacao],ISNULL(CONVERT(VARCHAR, [Dt_Modificacao], 120), '-')AS [Dt_Modificacao],ISNULL(CAST([Nr_Versao] AS VARCHAR), '-')AS [Nr_Versao]FROM [dbo].[CheckList_Alteracao_Jobs]ORDER BY [Dt_Modificacao] DESC ) AS D FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @JobsAlterados_Table = REPLACE( REPLACE( REPLACE( @JobsAlterados_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @JobsAlterados_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="300" bgcolor=#0B0B61>Nome Job</th><th width="200" bgcolor=#0B0B61>Habilitado</th><th width="200" bgcolor=#0B0B61>Data Cria??o</th> <th width="200" bgcolor=#0B0B61>Data Altera??o</th><th width="200" bgcolor=#0B0B61>Núm. Vers?o</th> </tr>' + REPLACE( REPLACE(@JobsAlterados_Table, '<', '<'), '>', '>') + '</table>' /***********************************************************************************************************************************--Jobs que Falharam - Header***********************************************************************************************************************************/DECLARE @JobsFailed_Header VARCHAR(MAX)SET @JobsFailed_Header = ''SET @JobsFailed_Header = @JobsFailed_Header + ' TOP 10 - Jobs que Falharam 'SET @JobsFailed_Header = @JobsFailed_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Jobs que Falharam - Informa??es------------------------------------------------------------------------------------------------------------------------------------ DECLARE @JobsFailed_Table VARCHAR(MAX) SET @JobsFailed_Table = CAST( ( SELECT td = [Job_Name] +'</td><td>' + [Status] + '</td><td>' + [Dt_Execucao] + '</td><td>' + [Run_Duration] + '</td><td>' + [SQL_Message] +'</td>' FROM (SELECTTOP 10[Job_Name], ISNULL([Status], '-')AS [Status], ISNULL(CONVERT(VARCHAR, [Dt_Execucao], 120), '-')AS [Dt_Execucao], ISNULL([Run_Duration], '-')AS [Run_Duration], ISNULL([SQL_Message], '-')AS [SQL_Message]FROM [dbo].[CheckList_Jobs_Failed]ORDER BY [Dt_Execucao] DESC ) AS D ORDER BY [Dt_Execucao] DESCFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @JobsFailed_Table = REPLACE( REPLACE( REPLACE(@JobsFailed_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @JobsFailed_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="300" bgcolor=#0B0B61>Nome Job</th><th width="200" bgcolor=#0B0B61>Status</th><th width="200" bgcolor=#0B0B61>Horário Execu??o</th> <th width="200" bgcolor=#0B0B61>Dura??o (hh:mm:ss)</th><th width="400" bgcolor=#0B0B61>Mensagem</th> </tr>' + REPLACE( REPLACE(@JobsFailed_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Jobs Demorados - Header***********************************************************************************************************************************/ DECLARE @TempoJobs_Header VARCHAR(MAX)SET @TempoJobs_Header = ''SET @TempoJobs_Header = @TempoJobs_Header + ' TOP 10 - Jobs Demorados ' SET @TempoJobs_Header = @TempoJobs_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Jobs Demorados - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @TempoJobs_Table VARCHAR(MAX) SET @TempoJobs_Table = CAST( ( SELECT td = [Job_Name] + '</td><td>' + [Status] + '</td><td>' + [Dt_Execucao] + '</td><td>' + [Run_Duration] + '</td><td>' + [SQL_Message] +'</td>' FROM (SELECTTOP 10[Job_Name], ISNULL([Status], '-')AS [Status], ISNULL(CONVERT(VARCHAR, [Dt_Execucao], 120), '-')AS [Dt_Execucao], ISNULL([Run_Duration], '-')AS [Run_Duration], ISNULL([SQL_Message], '-')AS [SQL_Message]FROM [dbo].[CheckList_Job_Demorados]ORDER BY [Run_Duration] DESC ) AS D FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @TempoJobs_Table = REPLACE( REPLACE( REPLACE(@TempoJobs_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @TempoJobs_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="300" bgcolor=#0B0B61>Nome Job</th><th width="200" bgcolor=#0B0B61>Status</th><th width="200" bgcolor=#0B0B61>Horário Execu??o</th> <th width="200" bgcolor=#0B0B61>Dura??o</th><th width="400" bgcolor=#0B0B61>Mensagem</th> </tr>' + REPLACE( REPLACE(@TempoJobs_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Queries Demoradas - Header***********************************************************************************************************************************/DECLARE @QueriesDemoradas_Header VARCHAR(MAX)SET @QueriesDemoradas_Header = ''SET @QueriesDemoradas_Header = @QueriesDemoradas_Header + ' TOP 10 - Queries Demoradas Dia Anterior (07:00 - 23:00) 'SET @QueriesDemoradas_Header = @QueriesDemoradas_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Queries Demoradas - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @QueriesDemoradas_Table VARCHAR(MAX) SET @QueriesDemoradas_Table = CAST( ( SELECT td =CASE WHEN [PrefixoQuery] = 'TOTAL'THEN ' bgcolor=#0B0B61>' + [PrefixoQuery]+ ''ELSE [PrefixoQuery] END + '</td><td>' +CASE WHEN [PrefixoQuery] = 'TOTAL'THEN ' bgcolor=#0B0B61>' + [QTD]+ ''ELSE [QTD] END + '</td><td>' +CASE WHEN [PrefixoQuery] = 'TOTAL'THEN ' bgcolor=#0B0B61>' + [Total]+ ''ELSE [Total] END + '</td><td>' +CASE WHEN [PrefixoQuery] = 'TOTAL'THEN ' bgcolor=#0B0B61>' + [Media]+ ''ELSE [Media] END + '</td><td>' + CASE WHEN [PrefixoQuery] = 'TOTAL'THEN ' bgcolor=#0B0B61>' + [Menor]+ ''ELSE [Menor] END + '</td><td>' + CASE WHEN [PrefixoQuery] = 'TOTAL'THEN ' bgcolor=#0B0B61>' + [Maior]+ ''ELSE [Maior] END + '</td><td>' + CASE WHEN [PrefixoQuery] = 'TOTAL'THEN ' bgcolor=#0B0B61>' + [Writes]+ ''ELSE [Writes] END + '</td><td>' +CASE WHEN [PrefixoQuery] = 'TOTAL'THEN ' bgcolor=#0B0B61>' + [CPU]+ ''ELSE [CPU] END + '</td><td>' +CASE WHEN [PrefixoQuery] = 'TOTAL'THEN ' bgcolor=#0B0B61>' + [Reads]+ ''ELSE [Reads] END + '</td>'FROM (SELECT[dbo].[fncRetira_Caractere_Invalido_XML] ([PrefixoQuery])AS [PrefixoQuery],ISNULL(CAST([QTD] AS VARCHAR), '-')AS [QTD],ISNULL(CAST([Total] AS VARCHAR), '-')AS [Total],ISNULL(CAST([Media] AS VARCHAR), '-')AS [Media],ISNULL(CAST([Menor] AS VARCHAR), '-')AS [Menor],ISNULL(CAST([Maior] AS VARCHAR), '-')AS [Maior],ISNULL(CAST([Writes] AS VARCHAR), '-')AS [Writes],ISNULL(CAST([CPU] AS VARCHAR), '-')AS [CPU],ISNULL(CAST([Reads] AS VARCHAR), '-')AS [Reads],[Ordem]FROM [dbo].[CheckList_Traces_Queries] ) AS D ORDER BY [Ordem], LEN([QTD]) DESC, [QTD] DESCFOR XML PATH( 'tr' )) AS VARCHAR(MAX) )-- Corre??o de BUG de caractere invalido no FOR XML (character (0x0000))SELECT @QueriesDemoradas_Table = REPLACE(@QueriesDemoradas_Table, '', '') SET @QueriesDemoradas_Table = REPLACE( REPLACE( REPLACE( REPLACE(@QueriesDemoradas_Table, '<', '<'), '>', '>'), '<td> ', '<td align=center '),'<td>', '<td align=center>') SET @QueriesDemoradas_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="400" bgcolor=#0B0B61>Prefixo Query (150 caracteres iniciais)</th><th width="150" bgcolor=#0B0B61>Qtd</th><th width="150" bgcolor=#0B0B61>Total (s)</th> <th width="150" bgcolor=#0B0B61>Média (s)</th><th width="150" bgcolor=#0B0B61>Menor (s)</th> <th width="150" bgcolor=#0B0B61>Maior (s)</th> <th width="150" bgcolor=#0B0B61>Writes</th> <th width="150" bgcolor=#0B0B61>CPU</th><th width="150" bgcolor=#0B0B61>Reads</th></tr>' + REPLACE( REPLACE(@QueriesDemoradas_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Queries Demoradas Geral - Header***********************************************************************************************************************************/ DECLARE @QueriesDemoradasGeral_Header VARCHAR(MAX)SET @QueriesDemoradasGeral_Header = ''SET @QueriesDemoradasGeral_Header = @QueriesDemoradasGeral_Header + ' TOP 10 - Queries Demoradas - ?ltimos 10 Dias (07:00 - 23:00) ' SET @QueriesDemoradasGeral_Header = @QueriesDemoradasGeral_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Queries Demoradas Geral - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @QueriesDemoradasGeral_Table VARCHAR(MAX) SET @QueriesDemoradasGeral_Table = CAST( ( SELECT td = [Data]+ '</td><td>' + [QTD]+'</td>' FROM (SELECT[Data], ISNULL(CAST([QTD] AS VARCHAR), '-')AS [QTD]FROM [dbo].[CheckList_Traces_Queries_Geral] ) AS D ORDER BY [Data] DESCFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @QueriesDemoradasGeral_Table = REPLACE( REPLACE( REPLACE(@QueriesDemoradasGeral_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @QueriesDemoradasGeral_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="200" bgcolor=#0B0B61>Data</th><th width="200" bgcolor=#0B0B61>Quantidade</th> </tr>' + REPLACE( REPLACE(@QueriesDemoradasGeral_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Contadores - Header***********************************************************************************************************************************/DECLARE @Contadores_Header VARCHAR(MAX)SET @Contadores_Header = ''SET @Contadores_Header = @Contadores_Header + ' Média Contadores Dia Anterior (07:00 - 23:00) ' SET @Contadores_Header = @Contadores_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Contadores - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @Contadores_Table VARCHAR(MAX) SET @Contadores_Table = CAST( ( SELECT td = [Hora] + '</td><td>' + [BatchRequests] + '</td><td>' + [CPU] + '</td><td>' + [Page_Life_Expectancy] + '</td><td>' + [User_Connection] +'</td><td>' + [Qtd_Queries_Lentas] + '</td><td>' + [Reads_Queries_Lentas] +'</td>' FROM ( SELECT[Hora],[BatchRequests],[CPU],[Page_Life_Expectancy],[User_Connection],[Qtd_Queries_Lentas],[Reads_Queries_Lentas]FROM [dbo].[CheckList_Contadores_Email] AS C ) AS D ORDER BY LEN([Hora]), [Hora]FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @Contadores_Table = REPLACE( REPLACE( REPLACE( @Contadores_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @Contadores_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="200" bgcolor=#0B0B61>Hora</th><th width="200" bgcolor=#0B0B61>Batch Requests</th><th width="200" bgcolor=#0B0B61>CPU</th> <th width="200" bgcolor=#0B0B61>Page Life Expectancy</th><th width="200" bgcolor=#0B0B61>Número de Conex?es</th><th width="200" bgcolor=#0B0B61>Qtd Queries Lentas</th><th width="200" bgcolor=#0B0B61>Reads Queries Lentas</th></tr>' + REPLACE( REPLACE(@Contadores_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Conexoes Abertas - Header***********************************************************************************************************************************/ DECLARE @ConexoesAbertas_Header VARCHAR(MAX)SET @ConexoesAbertas_Header = ''SET @ConexoesAbertas_Header = @ConexoesAbertas_Header + ' TOP 10 - Conex?es Abertas por Usuários ' SET @ConexoesAbertas_Header = @ConexoesAbertas_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Conexoes Abertas - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @ConexoesAbertas_Table VARCHAR(MAX) SET @ConexoesAbertas_Table = CAST( ( SELECT td = CASE WHEN [login_name] = 'TOTAL'THEN ' bgcolor=#0B0B61>' + [login_name]+ ''ELSE [login_name] END + '</td><td>' +CASE WHEN [login_name] = 'TOTAL 'THEN ' bgcolor=#0B0B61>' + [session_count]+ ''ELSE [session_count] END + '</td>' FROM (SELECTNr_Ordem,ISNULL([login_name], '-')AS [login_name], CAST([session_count] AS VARCHAR)AS [session_count]FROM [dbo].[CheckList_Conexao_Aberta_Email] ) AS D ORDER BY Nr_Ordem, CAST([session_count] AS INT) DESCFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) --SET @ConexoesAbertas_Table = REPLACE( REPLACE( REPLACE(@ConexoesAbertas_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @ConexoesAbertas_Table = REPLACE( REPLACE( REPLACE( REPLACE(@ConexoesAbertas_Table, '<', '<'), '>', '>'), '<td> ', '<td align=center '),'<td>', '<td align=center>')SET @ConexoesAbertas_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="400" bgcolor=#0B0B61>Login Name</th> <th width="200" bgcolor=#0B0B61>Qtd Conex?es</th> </tr>' + REPLACE( REPLACE(@ConexoesAbertas_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Fragmenta??o de ?ndices - Header***********************************************************************************************************************************/DECLARE @FragmentacaoIndice_Header VARCHAR(MAX)SET @FragmentacaoIndice_Header = ''SET @FragmentacaoIndice_Header = @FragmentacaoIndice_Header + ' TOP 10 - Fragmenta??o dos ?ndices 'SET @FragmentacaoIndice_Header = @FragmentacaoIndice_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Fragmenta??o de ?ndices - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @FragmentacaoIndice_Table VARCHAR(MAX) SET @FragmentacaoIndice_Table = CAST( (SELECT td = [Dt_Referencia] +'</td><td>' + [Nm_Database] +'</td><td>' + [Nm_Tabela] +'</td><td>' + [Nm_Indice] +'</td><td>' + [Avg_Fragmentation_In_Percent] +'</td><td>' + [Page_Count] +'</td><td>' + [Fill_Factor] +'</td><td>' + [Compressao] +'</td>'FROM (SELECTTOP 10ISNULL(CONVERT(VARCHAR, [Dt_Referencia], 120), '-')AS [Dt_Referencia], [Nm_Database], ISNULL([Nm_Tabela], '-')AS [Nm_Tabela], ISNULL([Nm_Indice], '-')AS [Nm_Indice],ISNULL(CAST([Avg_Fragmentation_In_Percent]AS VARCHAR), '-')AS [Avg_Fragmentation_In_Percent],ISNULL(CAST([Page_Count]AS VARCHAR), '-')AS [Page_Count], ISNULL(CAST([Fill_Factor]AS VARCHAR), '-')AS [Fill_Factor],ISNULL(CASE [Fl_Compressao]WHEN 0 THEN 'Sem Compress?o'WHEN 1 THEN 'Compress?o de Linha' WHEN 2 THEN 'Compressao de Página'END, '-') AS [Compressao]FROM [dbo].[CheckList_Fragmentacao_Indices]ORDER BY CAST(REPLACE([Avg_Fragmentation_In_Percent], '-', 0) AS NUMERIC(15,2)) DESC ) AS D FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @FragmentacaoIndice_Table = REPLACE( REPLACE( REPLACE( @FragmentacaoIndice_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @FragmentacaoIndice_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="200" bgcolor=#0B0B61>Referencia</th><th width="300" bgcolor=#0B0B61>Database</th><th width="300" bgcolor=#0B0B61>Tabela</th> <th width="200" bgcolor=#0B0B61>Indice</th><th width="200" bgcolor=#0B0B61>Fragmentacao (%)</th> <th width="200" bgcolor=#0B0B61>Qtd Páginas</th> <th width="200" bgcolor=#0B0B61>Fill Factor (%)</th> <th width="200" bgcolor=#0B0B61>Compress?o de Dados</th> </tr>' + REPLACE( REPLACE( @FragmentacaoIndice_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Waits Stats - Header***********************************************************************************************************************************/DECLARE @WaitsStats_Header VARCHAR(MAX)SET @WaitsStats_Header = ''SET @WaitsStats_Header = @WaitsStats_Header + ' TOP 10 - Waits Stats Dia Anterior (07:00 - 23:00) 'SET @WaitsStats_Header = @WaitsStats_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Waits Stats - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @WaitsStats_Table VARCHAR(MAX) SET @WaitsStats_Table = CAST( ( SELECT td = [WaitType]+'</td><td>' + [Max_Log]+'</td><td>' + [DIf_Wait_S]+'</td><td>' + [DIf_Resource_S]+'</td><td>' + [DIf_Signal_S]+'</td><td>' + [DIf_WaitCount]+'</td><td>' + [Last_Percentage] +'</td>' FROM ( SELECTTOP 10[WaitType], ISNULL(CONVERT(VARCHAR, [Max_Log], 120), '-') AS [Max_Log],ISNULL(CAST([DIf_Wait_S]AS VARCHAR), '-') AS [DIf_Wait_S], ISNULL(CAST([DIf_Resource_S]AS VARCHAR), '-') AS [DIf_Resource_S],ISNULL(CAST([DIf_Signal_S]AS VARCHAR), '-') AS [DIf_Signal_S], ISNULL(CAST([DIf_WaitCount]AS VARCHAR), '-') AS [DIf_WaitCount],ISNULL(CAST([Last_Percentage]AS VARCHAR), '-') AS [Last_Percentage]FROM [dbo].[CheckList_Waits_Stats]ORDER BY CAST(REPLACE([DIf_Wait_S], '-', 0) AS NUMERIC(15,2)) DESC ) AS D ORDER BY CAST(REPLACE([DIf_Wait_S], '-', 0) AS NUMERIC(15,2)) DESCFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @WaitsStats_Table = REPLACE( REPLACE( REPLACE( @WaitsStats_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @WaitsStats_Table ='<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="400" bgcolor=#0B0B61>WaitType</th><th width="200" bgcolor=#0B0B61>Data Log</th> <th width="200" bgcolor=#0B0B61>Wait (s)</th><th width="200" bgcolor=#0B0B61>Resource (s)</th> <th width="200" bgcolor=#0B0B61>Signal (s)</th> <th width="200" bgcolor=#0B0B61>Qtd Wait</th> <th width="200" bgcolor=#0B0B61>Last (%)</th> </tr>' + REPLACE( REPLACE( @WaitsStats_Table, '<', '<'), '>', '>') + '</table>' /***********************************************************************************************************************************--Alertas Sem CLEAR - Header***********************************************************************************************************************************/DECLARE @Alerta_Sem_Clear_Header VARCHAR(MAX)SET @Alerta_Sem_Clear_Header = ''SET @Alerta_Sem_Clear_Header = @Alerta_Sem_Clear_Header + ' Alertas Sem CLEAR 'SET @Alerta_Sem_Clear_Header = @Alerta_Sem_Clear_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Alertas Sem CLEAR - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @Alerta_Sem_Clear_Table VARCHAR(MAX) SET @Alerta_Sem_Clear_Table = CAST( ( SELECT td = [Nm_Alerta]+'</td><td>' + [Ds_Mensagem]+'</td><td>' + [Dt_Alerta]+ '</td><td>' + [Run_Duration]+'</td>'FROM ( SELECT[Nm_Alerta],ISNULL([Ds_Mensagem], '-') AS [Ds_Mensagem],ISNULL(CONVERT(VARCHAR, [Dt_Alerta], 120), '-') AS [Dt_Alerta],ISNULL([Run_Duration], '-') AS [Run_Duration]FROM [dbo].[CheckList_Alerta_Sem_Clear] ) AS D ORDER BY [Dt_Alerta]FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @Alerta_Sem_Clear_Table = REPLACE( REPLACE( REPLACE( @Alerta_Sem_Clear_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @Alerta_Sem_Clear_Table ='<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="300" bgcolor=#0B0B61>Nome Alerta</th><th width="900" bgcolor=#0B0B61>Mensagem</th><th width="200" bgcolor=#0B0B61>Data</th><th width="200" bgcolor=#0B0B61>Dura??o</th></tr>' + REPLACE( REPLACE( @Alerta_Sem_Clear_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Alertas - Header***********************************************************************************************************************************/DECLARE @Alerta_Header VARCHAR(MAX)SET @Alerta_Header = ''SET @Alerta_Header = @Alerta_Header + ' TOP 50 - Alertas do Dia Anterior 'SET @Alerta_Header = @Alerta_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Alertas - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @Alerta_Table VARCHAR(MAX) SET @Alerta_Table = CAST( ( SELECT td = [Nm_Alerta]+'</td><td>' + [Ds_Mensagem]+'</td><td>' + [Dt_Alerta]+ '</td><td>' + [Run_Duration]+'</td>' FROM ( SELECTTOP 50[Nm_Alerta],ISNULL([Ds_Mensagem], '-') AS [Ds_Mensagem],ISNULL(CONVERT(VARCHAR, [Dt_Alerta], 120), '-') AS [Dt_Alerta],ISNULL([Run_Duration], '-') AS [Run_Duration]FROM [dbo].[CheckList_Alerta]ORDER BY [Dt_Alerta] DESC ) AS D ORDER BY [Dt_Alerta] DESCFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @Alerta_Table = REPLACE( REPLACE( REPLACE( @Alerta_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @Alerta_Table ='<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="300" bgcolor=#0B0B61>Nome Alerta</th><th width="900" bgcolor=#0B0B61>Mensagem</th><th width="200" bgcolor=#0B0B61>Data</th><th width="200" bgcolor=#0B0B61>Dura??o</th></tr>' + REPLACE( REPLACE( @Alerta_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Login Failed - Header***********************************************************************************************************************************/DECLARE @LoginFailed_Header VARCHAR(MAX)SET @LoginFailed_Header = ''SET @LoginFailed_Header = @LoginFailed_Header + ' TOP 10 - Login Failed - SQL Server ' SET @LoginFailed_Header = @LoginFailed_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Login Failed - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @LoginFailed_Table VARCHAR(MAX) SET @LoginFailed_Table = CAST( ( SELECT td = CASE WHEN [Text] = 'TOTAL'THEN ' bgcolor=#0B0B61>' + [Text]+ ''ELSE [Text] END + '</td><td>' +CASE WHEN [Text] = 'TOTAL 'THEN ' bgcolor=#0B0B61>' + [Qt_Erro]+ ''ELSE [Qt_Erro] END + '</td>' FROM (SELECTTOP 10[Nr_Ordem],[Text],ISNULL(CAST([Qt_Erro] AS VARCHAR), '-') AS [Qt_Erro]FROM [dbo].[CheckList_SQLServer_LoginFailed_Email]ORDER BY CAST(REPLACE([Qt_Erro], '-', 0) AS INT) DESC ) AS D ORDER BY Nr_Ordem, CAST(REPLACE([Qt_Erro], '-', 0) AS INT) DESCFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) )SET @LoginFailed_Table = REPLACE( REPLACE( REPLACE( REPLACE(@LoginFailed_Table, '<', '<'), '>', '>'), '<td> ', '<td align=center '),'<td>', '<td align=center>') SET @LoginFailed_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="400" bgcolor=#0B0B61>Usuário</th><th width="200" bgcolor=#0B0B61>Qtd Erros</th></tr>' + REPLACE( REPLACE( @LoginFailed_Table, '<', '<'), '>', '>') + '</table>'/***********************************************************************************************************************************--Error Log SQL - Header***********************************************************************************************************************************/DECLARE @LogSQL_Header VARCHAR(MAX)SET @LogSQL_Header = ''SET @LogSQL_Header = @LogSQL_Header + ' TOP 100 - Error Log do SQL Server ' SET @LogSQL_Header = @LogSQL_Header + ''--------------------------------------------------------------------------------------------------------------------------------------Error Log SQL - Informa??es------------------------------------------------------------------------------------------------------------------------------------DECLARE @LogSQL_Table VARCHAR(MAX) SET @LogSQL_Table = CAST( ( SELECT td = [Dt_Log]+ '</td><td>' + [ProcessInfo] + '</td><td>' + [Text]+ '</td>' FROM (SELECTTOP 100ISNULL(CONVERT(VARCHAR, [Dt_Log], 120), '-') AS [Dt_Log], ISNULL([ProcessInfo], '-') AS [ProcessInfo], [Text] FROM [dbo].[CheckList_SQLServer_ErrorLog]ORDER BY [Dt_Log] DESC ) AS D ORDER BY [Dt_Log] DESCFOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) ) SET @LogSQL_Table = REPLACE( REPLACE( REPLACE(@LogSQL_Table, '<', '<'), '>', '>'), '<td>', '<td align=center>') SET @LogSQL_Table = '<table cellspacing="2" cellpadding="5" border="3">' +'<tr><th width="250" bgcolor=#0B0B61>Data Log</th><th width="150" bgcolor=#0B0B61>Processo</th><th width="400" bgcolor=#0B0B61>Mensagem</th> </tr>' + REPLACE( REPLACE( @LogSQL_Table, '<', '<'), '>', '>') + '</table>' /***********************************************************************************************************************************-- Se??o em branco para dar espa?o entre AS tabelas e os cabe?alhos***********************************************************************************************************************************/DECLARE @emptybody2 VARCHAR(MAX) SET @emptybody2 ='' SET @emptybody2 ='<table cellpadding="5" cellspacing="5" border="0">' + '<tr><th width="500"> </th></tr>'+ REPLACE( REPLACE( ISNULL(@emptybody2,''), '<', '<'), '>', '>')+ '</table>' -------------------------------------------------------------------------------------------------------------------------------------- Seta AS Informa??es do E-Mail------------------------------------------------------------------------------------------------------------------------------------DECLARE@importance AS VARCHAR(6) = 'High',@Reportdate DATETIME = GETDATE(),@recipientsList VARCHAR(8000),@subject AS VARCHAR(500),@EmailBody VARCHAR(MAX) = ''SELECT @subject = 'CheckList Diário do Banco de Dados - ' + @Nm_Empresa + ' - ' + @@SERVERNAMEIF ( @DisponibilidadeSQL_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @DisponibilidadeSQL_Header + @emptybody2 + @DisponibilidadeSQL_Table + @emptybody2-- Disponibilidade SQLIF ( @EspacoDisco_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @EspacoDisco_Header + @emptybody2 + @EspacoDisco_Table + @emptybody2-- Espa?o em DiscoIF ( @ArquivosDados_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @ArquivosDados_Header + @emptybody2 + @ArquivosDados_Table + @emptybody2-- Arquivos DadosIF ( @ArquivosLog_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @ArquivosLog_Header + @emptybody2 + @ArquivosLog_Table + @emptybody2-- Arquivos LogIF ( @CrescimentoBases_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @CrescimentoBases_Header + @emptybody2 + @CrescimentoBases_Table + @emptybody2-- Crescimento das BasesIF ( @CrescimentoTabelas_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @CrescimentoTabelas_Header + @emptybody2 + @CrescimentoTabelas_Table + @emptybody2-- Crescimento das TabelasIF ( @UtilizacaoArqWrites_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @UtilizacaoArqWrites_Header + @emptybody2 + @UtilizacaoArqWrites_Table + @emptybody2-- Utilizacao Arquivos - WrtiesIF ( @UtilizacaoArqReads_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @UtilizacaoArqReads_Header + @emptybody2 + @UtilizacaoArqReads_Table + @emptybody2-- Utilizacao Arquivos - ReadsIF ( @DatabaseSemBackup_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @DatabaseSemBackup_Header + @emptybody2 + @DatabaseSemBackup_Table + @emptybody2-- Databases Sem BackupIF ( @Backup_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @Backup_Header + @emptybody2 + @Backup_Table + @emptybody2-- Backups ExecutadosIF ( @QueriesRunning_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @QueriesRunning_Header + @emptybody2 + @QueriesRunning_Table + @emptybody2-- Queries em Execu??oIF ( @JobsRunning_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @JobsRunning_Header + @emptybody2 + @JobsRunning_Table + @emptybody2-- Jobs em Execu??oIF ( @JobsAlterados_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @JobsAlterados_Header + @emptybody2 + @JobsAlterados_Table + @emptybody2-- Jobs AlteradosIF ( @JobsFailed_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @JobsFailed_Header + @emptybody2 + @JobsFailed_Table + @emptybody2-- Jobs FailedIF ( @TempoJobs_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @TempoJobs_Header + @emptybody2 + @TempoJobs_Table + @emptybody2-- Jobs DemoradosIF ( @QueriesDemoradas_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @QueriesDemoradas_Header + @emptybody2 + @QueriesDemoradas_Table + @emptybody2-- Queries DemoradasIF ( @QueriesDemoradasGeral_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @QueriesDemoradasGeral_Header + @emptybody2 + @QueriesDemoradasGeral_Table + @emptybody2-- Queries Demoradas GeralIF ( @Contadores_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @Contadores_Header + @emptybody2 + @Contadores_Table + @emptybody2-- ContadoresIF ( @ConexoesAbertas_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @ConexoesAbertas_Header + @emptybody2 + @ConexoesAbertas_Table + @emptybody2-- Conexao AbertaIF ( @FragmentacaoIndice_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @FragmentacaoIndice_Header + @emptybody2 + @FragmentacaoIndice_Table + @emptybody2-- Fragmenta??o ?ndiceIF ( @WaitsStats_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @WaitsStats_Header + @emptybody2 + @WaitsStats_Table + @emptybody2-- Waits StatsIF ( @Alerta_Sem_Clear_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @Alerta_Sem_Clear_Header + @emptybody2 + @Alerta_Sem_Clear_Table + @emptybody2-- Alerta Sem CLEARIF ( @Alerta_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @Alerta_Header + @emptybody2 + @Alerta_Table + @emptybody2-- AlertasIF ( @LoginFailed_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @LoginFailed_Header + @emptybody2 + @LoginFailed_Table + @emptybody2-- Login FailedIF ( @LogSQL_Table IS NOT NULL )SELECT @EmailBody = @EmailBody + @LogSQL_Header + @emptybody2 + @LogSQL_Table + @emptybody2-- Error Log SQL/***********************************************************************************************************************************-- Envia o E-Mail do CheckList do Banco de Dados***********************************************************************************************************************************/EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = @Profile_Email,@recipients = @Ds_Email,@subject = @subject,@body = @EmailBody,@body_format = 'HTML', @importance = @importanceEND ................
................

In order to avoid copyright disputes, this page is only a partial summary.

To fulfill the demand for quickly locating and searching documents.

It is intelligent file search solution for home and business.

Literature Lottery

To fulfill the demand for quickly locating and searching documents.

Related download

Related searches