작업 스케줄러 일일체크 쿼리

Develop/DB - mssql

작업 스케줄러 일일체크 쿼리

개기획자 2020. 6. 15. 10:39

SELECT 
			 [jobs].name as [jobname]	
			, description				
			, LEFT(last_outcome_message, 10) AS [last_outcome_message]
			, last_run_date
			, CASE  convert(nvarchar(3),[sSCH].[enabled])
				WHEN 1 THEN 'Y'
				WHEN 0 THEN 'N'
			  END AS [IsEnabled]
			, CASE 
				WHEN convert(nvarchar(3),[freq_type],3) = 64 THEN 'SQL Server 시작 시 마다'
				WHEN convert(nvarchar(3),[freq_type],3) = 128 THEN '서버가 유휴 할때마다'
				WHEN convert(nvarchar(3),[freq_type],3) IN (4,8,16,32) THEN '되풀이'
				WHEN convert(nvarchar(3),[freq_type],3)  = 1 THEN '한번만'
			  END [ScheduleType]
			, CASE convert(nvarchar(3),[freq_type],3) 
				WHEN 1 THEN '1번'
				WHEN 4 THEN '매일'
				WHEN 8 THEN '매주'
				WHEN 16 THEN '매월'
				WHEN 32 THEN '매월'
				WHEN 64 THEN 'SQL Server 시작 시 마다'
				WHEN 128 THEN '서버가 유휴 할때마다'
			  END [Occurrence]
			, CASE convert(nvarchar(3),[freq_type],3) 
				WHEN 4 THEN '실행주기 : ' + CAST([freq_interval] AS VARCHAR(3)) + ' 일(s)'
				WHEN 8 THEN '실행주기 : ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
							+ ' 주(s) 매주'
							+ CASE WHEN [freq_interval] & 1 = 1 THEN '일' ELSE '' END
							+ CASE WHEN [freq_interval] & 2 = 2 THEN ', 월' ELSE '' END
							+ CASE WHEN [freq_interval] & 4 = 4 THEN ', 화' ELSE '' END
							+ CASE WHEN [freq_interval] & 8 = 8 THEN ', 수' ELSE '' END
							+ CASE WHEN [freq_interval] & 16 = 16 THEN ', 목' ELSE '' END
							+ CASE WHEN [freq_interval] & 32 = 32 THEN ', 금' ELSE '' END
							+ CASE WHEN [freq_interval] & 64 = 64 THEN ', 토' ELSE '' END
				WHEN 16 THEN '하루 한번 ' + CAST([freq_interval] AS VARCHAR(3)) 
							 + ' 모든 '
							 + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' 월(S)'
				WHEN 32 THEN '발생 '
							 + CASE [freq_relative_interval]
								WHEN 1 THEN '첫째'
								WHEN 2 THEN '둘째'
								WHEN 4 THEN '셋째'
								WHEN 8 THEN '넷째'
								WHEN 16 THEN '마지막'
							   END
							 + ' ' 
							 + CASE [freq_interval]
								WHEN 1 THEN '일'
								WHEN 2 THEN '월'
								WHEN 3 THEN '화'
								WHEN 4 THEN '수'
								WHEN 5 THEN '목'
								WHEN 6 THEN '금'
								WHEN 7 THEN '토'
								WHEN 8 THEN '일'
								WHEN 9 THEN '평일'
								WHEN 10 THEN '주말'
							   END
							 + ' 모든 ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
							 + ' 월(s)'
			  END AS [Recurrence]
			, CASE convert(nvarchar(3),[freq_subday_type],3)  
				WHEN 1 THEN '매일 한번 ' 
					  + STUFF(
						 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
										, 3, 0, ':')
									, 6, 0, ':')
				WHEN 2 THEN '주기적 발생 ' 
							+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' 초(s) 간격 ' 
							+ 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 '주기적 발생 ' 
							+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' 분(s) 간격 ' 
							+ 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 '주기적 발생 ' 
							+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' 시간(s) 간격 ' 
							+ 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]
		 FROM 
			[msdb].[dbo].[sysjobschedules] as [sJOBSCH] with(nolock)
			LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [sSCH] with(nolock) ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
			LEFT OUTER JOIN msdb.dbo.sysjobs as [jobs] WITH(NOLOCK) ON [sJOBSCH].job_id=jobs.job_id
			LEFT OUTER JOIN [msdb].[dbo].[sysjobservers] AS [JOBSER] with(nolock) on [sJOBSCH].[job_id] = [JOBSER].[job_id]
		ORDER BY jobname

저작자표시 비영리 동일조건 (새창열림)