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

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