MS SQL SERVER AGENT 모니터링 쿼리 (서버에이전트 모니터링)

job실행 SP msdb.dbo.sp_start_job @job_name = 'jobName'

MSSQL 서버 에이전트 모니터링 쿼리.

 ----------------------------------------------------------------------------------------------------------------------------------------------

--SQL SERVER AGENT 모니터링

SELECT 

A.name 

, A.description  

, B.last_run_date 

, B.last_outcome_message

, B.last_run_date 

, C.next_run_date 

 FROM msdb.dbo.sysjobs AS A WITH (NOLOCK)

 INNER JOIN msdb.dbo.SYSJOBSERVERS AS B WITH (NOLOCK)  ON A.job_id  = B.job_id 

 LEFT OUTER JOIN msdb.dbo.sysjobschedules AS C WITH (NOLOCK) ON A.job_id = C.job_id 

 --WHERE SYS_SRVS.last_run_outcome = 0  --실패작업 , 1-- 성공작업

----------------------------------------------------------------------------------------------------------------------------------------------

--SQL SERVER AGENT 모니터링

SELECT 

A.job_id

, A.name

, A.enabled

, description

, A.date_created

, A.date_modified

, D.step_id

, D.step_name

, D.subsystem

, D.command 

FROM msdb.dbo.sysjobs A WITH (NOLOCK)

INNER JOIN msdb.dbo.sysjobschedules B WITH (NOLOCK) ON A.job_id = B.job_id 

INNER JOIN msdb.dbo.sysschedules C WITH (NOLOCK) ON B.schedule_id = C.schedule_id 

INNER JOIN msdb.dbo.sysjobsteps D WITH (NOLOCK) ON A.job_id = D.job_id 

ORDER BY A.job_id, D.step_id

----------------------------------------------------------------------------------------------------------------------------------------------

--SQL SERVER AGENT 모니터링

SELECT

A.Name

, Result = Case When B.Last_Run_OutCome = 1 Then 'Success' 

Else 'Fail' 

End

, B.Last_Run_Date

, Convert(Varchar(10),(B.Last_Run_Time / 10000))+'시'+CONVERT(VARCHAR(10),(B.Last_Run_Time % 10000) / 100)+'분' AS Last_Run_Time

, C.Next_Run_Date

, Convert(Varchar(10),(C.Next_Run_Time / 10000))+'시'+CONVERT(VARCHAR(10),(C.Next_Run_Time % 10000) / 100)+'분' AS Next_Run_Time

, B.Last_Run_Duration 

FROM msdb.dbo.sysjobs AS A WITH(READUNCOMMITTED) 

LEFT JOIN msdb.dbo.sysjobservers AS B WITH(READUNCOMMITTED) ON A.Job_Id = B.Job_Id 

LEFT JOIN msdb.dbo.sysjobschedules AS C WITH(READUNCOMMITTED) ON A.Job_Id = C.Job_Id 

--WHERE 

--      SJ.Enabled = 1 

--      AND SJSCH.Next_Run_Date IS NOT NULL 

ORDER BY B.Last_Run_Date, B.Last_Run_Time

----------------------------------------------------------------------------------------------------------------------------------------------

참고 출처

http://www.sqler.com/bColumn/271911 

http://bbolltang.tistory.com/10