USE Traces
GO
/*******************************************************************************************************************************
-- Tabela de controle que será utilizada para armazenar o Historico dos Alertas.
*******************************************************************************************************************************/
CREATE TABLE [dbo].[Alerta] (
[Id_Alerta] INT IDENTITY PRIMARY KEY,
[Nm_Alerta] VARCHAR(200),
[Ds_Mensagem] VARCHAR(2000),
[Fl_Tipo] TINYINT, -- 0: CLEAR / 1: ALERTA
[Dt_Alerta] DATETIME DEFAULT(GETDATE())
)
GO
/*******************************************************************************************************************************
-- ALERTA: LOG FULL
*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[stpAlerta_Log_Full]
AS
BEGIN
SET NOCOUNT ON
-- 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 TINYINT,
@ResultadoWhoisactiveHeader VARCHAR(MAX), @ResultadoWhoisactiveTable VARCHAR(MAX)
-- Seta as variaveis
SELECT @LOG = 85, -- 85 %
@Tamanho_Minimo_Alerta_log = 100000 -- 100 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 [Nm_Alerta] = 'Arquivo de Log Full' )
-- 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),
[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 se existe algum LOG com mais de 85 % de utilização
*******************************************************************************************************************************/
IF EXISTS(
SELECT db.[name] AS [Database Name],
db.[recovery_model_desc] AS [Recovery Model],
db.[log_reuse_wait_desc] AS [Log Reuse Wait DESCription],
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 [Percente_Log_Used] ,
db.[compatibility_level] AS [DB Compatibility Level] ,
db.[page_verify_option_desc] AS [Page Verify Option]
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
AND (
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
) > @LOG -- Maior que 85 %
)
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][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
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 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]
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
AND (
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
) > @LOG
) 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] + ' | '
+ '' + [start_time] + ' | '
+ '' + [status] + ' | '
+ '' + [session_id] + ' | '
+ '' + [blocking_session_id] + ' | '
+ '' + [Wait] + ' | '
+ '' + [open_tran_count] + ' | '
+ '' + [CPU] + ' | '
+ '' + [reads] + ' | '
+ '' + [writes] + ' | '
+ '' + [sql_command] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT ISNULL([dd hh:mm:ss.mss], '-') AS [Duração],
ISNULL([database_name], '-') AS [database_name],
ISNULL([login_name], '-') AS [login_name],
ISNULL(CONVERT(VARCHAR(20), [start_time], 120), '-') AS [start_time],
ISNULL([status], '-') AS [status],
ISNULL(CAST([session_id] AS VARCHAR), '-') AS [session_id],
ISNULL(CAST([blocking_session_id] AS VARCHAR), '-') AS [blocking_session_id],
ISNULL([wait_info], '-') AS [Wait],
ISNULL(CAST([open_tran_count] AS VARCHAR), '-') AS [open_tran_count],
ISNULL([CPU], '-') AS [CPU],
ISNULL([reads], '-') AS [reads],
ISNULL([writes], '-') AS [writes],
ISNULL(SUBSTRING([sql_command], 1, 300), '-') AS [sql_command]
FROM #Resultado_WhoisActive
) AS D ORDER BY [start_time]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @ResultadoWhoisactiveTable = REPLACE( REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @ResultadoWhoisactiveTable =
''
+ '
[dd hh:mm:ss.mss] |
Database |
Login |
Hora Início |
Status |
ID Sessão |
ID Sessão Bloqueando |
Wait |
Transações Abertas |
CPU |
Reads |
Writes |
Query |
'
+ 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 85% de utilização no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaLogHeader + @EmptyBodyEmail + @AlertaLogTable + @EmptyBodyEmail +
@ResultadoWhoisactiveHeader + @EmptyBodyEmail + @ResultadoWhoisactiveTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- ALERTA - ENVIA O EMAIL
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'Gmail',
@recipients = 'seuemail@dominio.com',
@subject = @Subject,
@body = @EmailBody,
@body_format = 'HTML',
@importance = @Importance
/*******************************************************************************************************************************
-- Insere um Registro na Tabela de Controle dos Alertas -> Fl_Tipo = 1 : ALERTA
*******************************************************************************************************************************/
INSERT INTO [dbo].[Alerta] ( [Nm_Alerta], [Ds_Mensagem], [Fl_Tipo] )
SELECT 'Arquivo de Log Full', @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][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
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 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]
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
) 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] + ' | '
+ '' + [start_time] + ' | '
+ '' + [status] + ' | '
+ '' + [session_id] + ' | '
+ '' + [blocking_session_id] + ' | '
+ '' + [Wait] + ' | '
+ '' + [open_tran_count] + ' | '
+ '' + [CPU] + ' | '
+ '' + [reads] + ' | '
+ '' + [writes] + ' | '
+ '' + [sql_command] + ' | '
FROM (
-- Dados da Tabela do EMAIL
SELECT ISNULL([dd hh:mm:ss.mss], '-') AS [Duração],
ISNULL([database_name], '-') AS [database_name],
ISNULL([login_name], '-') AS [login_name],
ISNULL(CONVERT(VARCHAR(20), [start_time], 120), '-') AS [start_time],
ISNULL([status], '-') AS [status],
ISNULL(CAST([session_id] AS VARCHAR), '-') AS [session_id],
ISNULL(CAST([blocking_session_id] AS VARCHAR), '-') AS [blocking_session_id],
ISNULL([wait_info], '-') AS [Wait],
ISNULL(CAST([open_tran_count] AS VARCHAR), '-') AS [open_tran_count],
ISNULL([CPU], '-') AS [CPU],
ISNULL([reads], '-') AS [reads],
ISNULL([writes], '-') AS [writes],
ISNULL(SUBSTRING([sql_command], 1, 300), '-') AS [sql_command]
FROM #Resultado_WhoisActive
) AS D ORDER BY [start_time]
FOR XML PATH( 'tr' ), TYPE) AS VARCHAR(MAX)
)
-- Corrige a Formatação da Tabela
SET @ResultadoWhoisactiveTable = REPLACE( REPLACE( REPLACE( @ResultadoWhoisactiveTable, '<', '<'), '>', '>'), '', ' | ')
-- Títulos da Tabela do EMAIL
SET @ResultadoWhoisactiveTable =
''
+ '
[dd hh:mm:ss.mss] |
Database |
Login |
Hora Início |
Status |
ID Sessão |
ID Sessão Bloqueando |
Wait |
Transações Abertas |
CPU |
Reads |
Writes |
Query |
'
+ 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 85% de utilização no Servidor: ' + @@SERVERNAME,
@EmailBody = @AlertaLogHeader + @EmptyBodyEmail + @AlertaLogTable + @EmptyBodyEmail +
@ResultadoWhoisactiveHeader + @EmptyBodyEmail + @ResultadoWhoisactiveTable + @EmptyBodyEmail
/*******************************************************************************************************************************
-- ALERTA - ENVIA O EMAIL
*******************************************************************************************************************************/
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'Gmail',
@recipients = 'seuemail@dominio.com',
@subject = @Subject,
@body = @EmailBody,
@body_format = 'HTML',
@importance = @Importance
/*******************************************************************************************************************************
-- Insere um Registro na Tabela de Controle dos Alertas -> Fl_Tipo = 0 : CLEAR
*******************************************************************************************************************************/
INSERT INTO [dbo].[Alerta] ( [Nm_Alerta], [Ds_Mensagem], [Fl_Tipo] )
SELECT 'Arquivo de Log Full', @Subject, 0
END
END -- FIM - CLEAR
END
GO
USE [msdb]
GO
/***********************************************************************************************************************************
-- CRIA JOB: [DBA - Alertas Banco de Dados]
***********************************************************************************************************************************/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
------------------------------------------------------------------------------------------------------------------------------------
-- Seleciona a Categoria do JOB
------------------------------------------------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT [name] FROM [msdb].[dbo].[syscategories] WHERE [name] = N'Database Maintenance' AND [category_class] = 1)
BEGIN
EXEC @ReturnCode = [msdb].[dbo].[sp_add_category] @class = N'JOB', @type = N'LOCAL', @name = N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = [msdb].[dbo].[sp_add_job]
@job_name = N'DBA - Alertas Banco de Dados',
@enabled = 1,
@notify_level_eventlog = 0,
@notify_level_email = 0,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description = N'No description available.',
@category_name = N'Database Maintenance',
@owner_login_name = N'sa',
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
------------------------------------------------------------------------------------------------------------------------------------
-- Cria o Step 1 do JOB - DBA - Alertas Banco de Dados
------------------------------------------------------------------------------------------------------------------------------------
EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobstep]
@job_id = @jobId,
@step_name = N'DBA - Alertas Banco de Dados',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = N'TSQL',
@command = N'-- Executado a cada minuto
EXEC [dbo].[stpAlerta_Log_Full]',
@database_name = N'Traces',
@flags = 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = [msdb].[dbo].[sp_update_job] @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
------------------------------------------------------------------------------------------------------------------------------------
-- Cria o Schedule do JOB
------------------------------------------------------------------------------------------------------------------------------------
DECLARE @Dt_Atual VARCHAR(8) = CONVERT(VARCHAR(8), GETDATE(), 112)
EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobschedule]
@job_id = @jobId,
@name = N'DBA - Alertas Banco de Dados',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_start_date = @Dt_Atual,
@active_end_date = 99991231,
@active_start_time = 30,
@active_end_time = 235959,
@schedule_uid = N'06818416-75e2-495a-a2b1-fd9215a660a4'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobserver] @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: |