SQL Agent 작업 실행 상태 확인하기

Q&A에 올라온 질문 내용에 대한 스크립트입니다.

SQL Agent에 등록되어 있는 작업이 현재 실행중인지를 확인하는 프로시저입니다.

 

간단할꺼라 생각했는데, 쉽게 확인할 수 있는 사항이 아니었네요~

시스템 테이블과 저장 프로시저들을 참고하여 한 번 만들어봤습니다.

SQL 2000에서도 제대로 돌아가기 위해 결과테이블을 테이블 변수 대신 임시테이블로 정의하였습니다.

SQL 2005에서는 테이블 변수에다가 execute 결과를 저장할 수가 있지만, SQL 2000에서는 안되어서요~^^

참고하시기 바랍니다.

 

 

CREATE PROC USP_CHECK_JOB_STATUS

             @job_name sysname AS

SET NOCOUNT ON

 

DECLARE @is_sysadmin INT 

DECLARE @job_owner   sysname 

DECLARE @job_id uniqueidentifier

 

IF OBJECT_ID('TEMPDB..#job_execution_state') IS NOT NULL

DROP TABLE #job_execution_state

 

CREATE TABLE #job_execution_state

                           (

                                        job_id                UNIQUEIDENTIFIER NOT NULL, 

            last_run_date         INT              NOT NULL, 

            last_run_time         INT              NOT NULL, 

            next_run_date         INT              NOT NULL, 

            next_run_time         INT              NOT NULL, 

            next_run_schedule_id  INT              NOT NULL, 

            requested_to_run      INT              NOT NULL, -- BOOL 

            request_source        INT              NOT NULL, 

            request_source_id     sysname          COLLATE database_default NULL, 

            running               INT              NOT NULL, -- BOOL 

            current_step          INT              NOT NULL, 

            current_retry_attempt INT              NOT NULL, 

            job_state             INT              NOT NULL) 

 

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) 

SELECT @job_owner = SUSER_SNAME()

SELECT @job_id = job_id FROM msdb.dbo.sysjobs where name = @job_name

 

IF @job_id IS NOT NULL

             BEGIN

                           INSERT INTO #job_execution_state

                           EXEC master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id

 

                           IF EXISTS (SELECT * FROM #job_execution_state)

                                        SELECT @job_name AS Name, running AS Status, CASE WHEN running = 1 THEN '실행중' ELSE '실행중이지 않음' END AS Comments

                                        FROM #job_execution_state

                           ELSE

                                        SELECT @job_name as Name, -1 as Status, '해당 작업이 없습니다.' AS Comments

             END

ELSE

             SELECT @job_name as Name, -1 as Status, '해당 작업이 없습니다.' AS Comments

 

SET NOCOUNT OFF

GO

 

 

 

USP_CHECK_JOB_STATUS 'TEST'

/*

Name     Status      Comments

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

TEST     1           실행중

*/

 

USP_CHECK_JOB_STATUS 'TEST'

/*

Name     Status      Comments

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

TEST     0           실행중이지 않음

*/

 

USP_CHECK_JOB_STATUS 'TEST1'

/*

Name     Status      Comments

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

TEST1     -1         해당 작업이 없습니다.

*/