Olá pessoal,
Tudo bem ?
Nesse post vou compartilhar com vocês uma necessidade que tive na sexta-feira, onde um servidor de arquivos ficaria desligado por algumas horas para manutenção e precisava identificar quais os jobs que acessavam esse servidor e por isso, seriam impactados por essa manutenção, ou seja, buscar uma string no código de SP’s que são chamadas por jobs do SQL Agent.
Um outro exemplo em que isso pode ser bem útil, é para identificar os jobs que acessam uma determinada tabela do banco de dados, caso ela precise sofrer alguma alteração ou manutenção, por exemplo, e você precise dessa informação para avaliar os impactos.
Pois bem, para conseguir automatizar isso, criei uma SP que irá varrer todos os databases da sua instância (utilizando a sp_msforeachdb), lendo a view sys.sql_modules de cada banco. Após identificar todas as SP’s ou funções que possuem a determinada string que você está buscando, a rotina faz um cruzamento dessas informações com as views do SQL Agent para identificar quais jobs que chamam essas SP’s.
Código fonte da SP
Para conseguir realizar essa verificação, basta utilizar o código-fonte abaixo, que também está disponível neste GitHub. Fique à vontade para sugerir alterações e melhorias. Uma delas, é que a SP abaixo funcione quando o job chama uma SP, que chama outra SP e esta sim, possui a string que estamos buscando.
USE [master]
GO
CREATE PROCEDURE [dbo].[stpBusca_String_Rotinas_Job]
@Ds_Busca [varchar](100)
AS
BEGIN
IF (OBJECT_ID('tempdb..#Resultado') IS NOT NULL) DROP TABLE #Resultado
CREATE TABLE #Resultado (
Ds_Database SYSNAME NULL,
Ds_Objeto SYSNAME NULL,
Ds_Schema SYSNAME NULL,
Ds_Tipo VARCHAR(100) NULL
)
DECLARE @Query VARCHAR(MAX) = '
SELECT
DB_NAME(DB_ID(''?'')) AS Ds_Database,
B.name AS Ds_Objeto,
C.name AS Ds_Schema,
B.type_desc AS Ds_Tipo
FROM
[?].sys.sql_modules A WITH(NOLOCK)
JOIN [?].sys.objects B WITH(NOLOCK) ON A.object_id = B.object_id
JOIN [?].sys.schemas C WITH(NOLOCK) ON B.schema_id = C.schema_id
WHERE
A.definition LIKE ''%' + @Ds_Busca + '%''
'
INSERT INTO #Resultado
EXEC master.sys.sp_msforeachdb @Query
SELECT
C.Ds_Database,
C.Ds_Schema,
C.Ds_Objeto,
A.[name] AS job_name,
A.[enabled],
B.step_id,
B.step_name,
B.[database_name],
(CASE WHEN B.last_run_date != 0 THEN msdb.dbo.agent_datetime(B.last_run_date, B.last_run_time) ELSE NULL END) AS last_run,
REPLACE(REPLACE(REPLACE(B.[command], CHAR(10) + CHAR(13), ' '), CHAR(13), ' '), CHAR(10), ' ') AS [ExecutableCommand],
E.[name] AS [JobScheduleName],
CASE
WHEN E.[freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
WHEN E.[freq_type] = 128 THEN 'Start whenever the CPUs become idle'
WHEN E.[freq_type] IN (4,8,16,32) THEN 'Recurring'
WHEN E.[freq_type] = 1 THEN 'One Time'
END [ScheduleType],
CASE E.[freq_type]
WHEN 1 THEN 'One Time'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
WHEN 128 THEN 'Start whenever the CPUs become idle'
END [Occurrence],
CASE E.[freq_type]
WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' week(s) on '
+ CASE WHEN E.[freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
+ CASE WHEN E.[freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
+ CASE WHEN E.[freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
+ CASE WHEN E.[freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
+ CASE WHEN E.[freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
+ CASE WHEN E.[freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
+ CASE WHEN E.[freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) + ' of every ' + CAST(E.[freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
WHEN 32 THEN 'Occurs on '
+ CASE E.[freq_relative_interval]
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 4 THEN 'Third'
WHEN 8 THEN 'Fourth'
WHEN 16 THEN 'Last'
END
+ ' '
+ CASE E.[freq_interval]
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'Day'
WHEN 9 THEN 'Weekday'
WHEN 10 THEN 'Weekend day'
END
+ ' of every ' + CAST(E.[freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
END AS [Recurrence],
CASE E.[freq_subday_type]
WHEN 1 THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN 2 THEN 'Occurs every ' + CAST(E.[freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN 4 THEN 'Occurs every ' + CAST(E.[freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN 8 THEN 'Occurs every ' + CAST(E.[freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
END [Frequency],
STUFF(STUFF(CAST(E.[active_start_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') AS [ScheduleUsageStartDate],
STUFF(STUFF(CAST(E.[active_end_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') AS [ScheduleUsageEndDate]
FROM
msdb.dbo.sysjobs A WITH(NOLOCK)
JOIN msdb.dbo.sysjobsteps B WITH(NOLOCK) ON A.job_id = B.job_id
JOIN #Resultado C ON B.command LIKE '%' + C.Ds_Objeto + '%'
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS D ON [A].[job_id] = D.[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS E ON D.[schedule_id] = E.[schedule_id]
WHERE
C.Ds_Database = B.[database_name]
OR B.command LIKE '%' + C.Ds_Database + '%'
END
Exemplo de uso:
CREATE PROCEDURE dbo.stpConsulta_Feriado_SP
AS
BEGIN
SELECT * FROM dirceuresende.dbo.Feriado
WHERE Sg_UF = 'SP'
END
GO
CREATE PROCEDURE dbo.stpConsulta_Feriado_ES
AS
BEGIN
SELECT * FROM dirceuresende.dbo.Feriado
WHERE Sg_UF = 'ES'
END
É isso aí, pessoal! Espero que tenham gostado dessa SP, um abraço e até o próximo post.
Comentários (0)
Carregando comentários…