SQL Agent 실행시간 변경하기 (run_time to datetime)

안녕하세요 

마푸입니다. 

sysjobhistory에서 SQL Server의 모니터링 체크를 진행하는데요 

예전에 작성한 쿼리에서는 run_time 컬럼값을 convert() 함수를 이용해서 계산하는 방식으로 했었는데 

agent_datetime() 이라는 함수가 있네요 헐.. 이렇게 간편하디니..

공유차 스크랩합니다.  

SQL Server – Get values as DATETIME from sysjobhistory

July 22, 2013VishalLeave a commentGo to comments

This is a follow-up post on SQL Server – Check SQL Agent Job History using T-SQL.

SQL Server Agent stores SQL jobs history in sysjobhistory. It has two different columns for date and time, Run_Date and Run_Time. Since this is not available as DATETIME we cannot filter based on certain criteria such as jobs that run in last 24 hours.

There is a system function available in msdb database which takes these two columns as input converts output to a DATETIME data type, dbo.agent_datetime. You can use this function as below:

SELECT        TOP 5

[JobName]     = JOB.name,

       [StepName]    = HIST.step_name,

              [RunDateTime] = dbo.agent_datetime(HIST.run_date,HIST.run_time)

FROM          sysjobs JOB

INNER JOIN    sysjobhistory HIST ON HIST.job_id = JOB.job_id

ORDERBY      HIST.run_date, HIST.run_time

Result Set:

JobName                  StepName                            RunDateTime

syspolicy_purge_history  Verify that automation is enabled.  2013-01-29 02:00:00.000

syspolicy_purge_history  Purge history.                      2013-01-29 02:00:00.000

syspolicy_purge_history  Erase Phantom System Health Records 2013-01-29 02:00:00.000

syspolicy_purge_history  (Job outcome)                       2013-01-29 02:00:00.000

syspolicy_purge_history  Verify that automation is enabled.  2013-01-30 02:00:00.000

 

(5 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Share this: