2013. 1. 14. 15:09
SQL SERVER
출처:http://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/
--Agent job 모니터링 Proc
alter procedure usp_errorjob
ASSET NOCOUNT ON;
select B.name,
CASE
WHEN A.[run_date] IS NULL OR A.[run_time] IS NULL THEN NULL
ELSE CAST(CAST(A.[run_date] AS CHAR(8)) + ' '
+ STUFF( STUFF(RIGHT('000000' + CAST(A.[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':') , 6, 0, ':') AS DATETIME)
END AS [StartDateTime] ,
STUFF(
STUFF(RIGHT('000000' + CAST([A].[run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)],
CASE A.[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'Running' -- In Progress
END AS [RunStatus]
, A.message from
[msdb].[dbo].[sysjobhistory] AS A
inner join [msdb].[dbo].[sysjobs] B
ON A.job_id = B.job_id
where convert(datetime, cast(A.run_date as varchar(20)), 121) > getdate() -7--성공과 실행중인 JOB 제외
AND A.run_status NOT IN (1)
AND B.enabled =1 --사용중인 JOB
AND name not like '%ERRORLOG Cycling%' --원래 실패할 수 있기 때문에 이 작업은 뺀다.
order by [StartDateTime] DESC
--agent job 설정 & 스케줄 정보 확인하기
SELECT [sJOB].[job_id] AS [JobID] , [sJOB].[name] AS [JobName] , [sDBP].[name] AS [JobOwner] , [sCAT].[name] AS [JobCategory] , [sJOB].[description] AS [JobDescription] , CASE [sJOB].[enabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS [IsEnabled] , [sJOB].[date_created] AS [JobCreatedOn] , [sJOB].[date_modified] AS [JobLastModifiedOn] , [sSVR].[name] AS [OriginatingServerName] , [sJSTP].[step_id] AS [JobStartStepNo] , [sJSTP].[step_name] AS [JobStartStepName] , CASE WHEN [sSCH].[schedule_uid] IS NULL THEN 'No' ELSE 'Yes' END AS [IsScheduled] , [sSCH].[schedule_uid] AS [JobScheduleID] , [sSCH].[name] AS [JobScheduleName] , CASE [sJOB].[delete_level] WHEN 0 THEN 'Never' WHEN 1 THEN 'On Success' WHEN 2 THEN 'On Failure' WHEN 3 THEN 'On Completion' END AS [JobDeletionCriterion] FROM [msdb].[dbo].[sysjobs] AS [sJOB] LEFT JOIN [msdb].[sys].[servers] AS [sSVR] ON [sJOB].[originating_server_id] = [sSVR].[server_id] LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id] LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP] ON [sJOB].[job_id] = [sJSTP].[job_id] AND [sJOB].[start_step_id] = [sJSTP].[step_id] LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid] LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id] LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id] ORDER BY [JobName]
--마지막 실행 상세정보+ 다음 실행스케쥴
SELECT [sJOB].[job_id] AS [JobID] , [sJOB].[name] AS [JobName] , CASE WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL ELSE CAST( CAST([sJOBH].[run_date] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [LastRunDateTime] , CASE [sJOBH].[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'Running' END AS [LastRunStatus] , STUFF( STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS [LastRunDuration (HH:MM:SS)] , [sJOBH].[message] AS [LastRunStatusMessage] , CASE [sJOBSCH].[NextRunDate] WHEN 0 THEN NULL ELSE CAST( CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [NextRunDateTime] FROM [msdb].[dbo].[sysjobs] AS [sJOB] LEFT JOIN ( SELECT [job_id] , MIN([next_run_date]) AS [NextRunDate] , MIN([next_run_time]) AS [NextRunTime] FROM [msdb].[dbo].[sysjobschedules] GROUP BY [job_id] ) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id] LEFT JOIN ( SELECT [job_id] , [run_date] , [run_time] , [run_status] , [run_duration] , [message] , ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber FROM [msdb].[dbo].[sysjobhistory] WHERE [step_id] = 0 ) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id] AND [sJOBH].[RowNumber] = 1 ORDER BY [JobName]
--agent job 스케쥴 정보 보는 쿼리
SELECT [schedule_uid] AS [ScheduleID] , [name] AS [ScheduleName] , CASE [enabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS [IsEnabled] , CASE WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts' WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle' WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring' WHEN [freq_type] = 1 THEN 'One Time' END [ScheduleType] , CASE [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 [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 [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' WHEN 32 THEN 'Occurs on ' + CASE [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 [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([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' END AS [Recurrence] , CASE [freq_subday_type] WHEN 1 THEN 'Occurs once at ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 2 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 4 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 8 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') END [Frequency] , STUFF( STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-') , 8, 0, '-') AS [ScheduleUsageStartDate] , STUFF( STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-') , 8, 0, '-') AS [ScheduleUsageEndDate] , [date_created] AS [ScheduleCreatedOn] , [date_modified] AS [ScheduleLastModifiedOn] FROM [msdb].[dbo].[sysschedules] ORDER BY [ScheduleName]
'SQL SERVER' 카테고리의 다른 글
SQL SERVER2008에서 SQL SERVER2000으로 링크드 서버 걸때 (0) | 2013.02.27 |
---|---|
SQL SERVER dbowner 변경하기 (0) | 2013.01.23 |
@schedule_uid은(는) 프로시저 sp_add_jobschedule의 매개 변수가 아닙니다. 해결법 (0) | 2012.09.26 |
칼럼 암호화 하기 (0) | 2012.04.19 |
Index 관리용 쿼리 (0) | 2011.07.07 |