sp_job

오래 돌고있는 SQL Server Agent Job을 확인하기 위한 필요성을 느껴 sp_job을 작성하였습니다.

Sysprocesses 와 sysJobs,sysJobsActivity 기반으로 동작합니다.

2000버전도 함께 공유합니다.

USE master

Go

/**************************************

SP_NAME : dbo.sp_job

SP_LOCATION : masterDB

SP_DB : master

SP_BUSINESS : Check the Running Job with Sysprocesses

SP_CREATE_DATE : 2014-11-19

SP_AUTHOR : Taewook Cha (SQLDBWook@gmail.com)

SP_MODIFY_LOG :

Project No | Sequence No | Modified Date | Name | Description

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

| #01. | 2014-10-21 | TWCha | Create

SP_RESULT_SET :

SP_EXEC_SAMPLE :

EXEC sp_job

REFERENCES :

**************************************/

CREATE PROCEDURE dbo.sp_job

AS

BEGIN

SELECT SJV.NAME AS JOB_NAME

, SJV.JOB_ID AS JOB_ID

, SJV.ORIGINATING_SERVER AS JOB_ORIGINATING_SERVER

, SJA.RUN_REQUESTED_DATE AS JOB_RUN_REQUESTED_DATE

, SYSP.SYSP_SPID

, SYSP.SYSP_WAIT_TIME_MS

, SYSP.SYSP_CPU_TIME_MS

, SYSP.SYSP_PHY_IO

, SYSP.SYSP_THREAD_STAT

, SYSP.SYSP_CMD_NOW

, SYSP.SYSP_LOGIN_NAME

, SYSP.SYSP_RUNNING_TIME_SEC

 FROM msdb.dbo.sysjobs_view AS SJV 

INNER JOIN msdb.dbo.sysjobactivity AS SJA

 ON SJV.job_id = SJA.job_id

 LEFT JOIN ( SELECT RIGHT(SUBSTRING(program_name,32,32),12) AS SYSP_JOB_ID_RIGHT_12

--, program_name

, spid AS SYSP_SPID

, waittime AS SYSP_WAIT_TIME_MS

, cpu AS SYSP_CPU_TIME_MS

, physical_io AS SYSP_PHY_IO

, status AS SYSP_THREAD_STAT

, cmd AS SYSP_CMD_NOW

, loginame AS SYSP_LOGIN_NAME

, datediff(SECOND,login_time,getdate()) AS SYSP_RUNNING_TIME_SEC

 FROM sys.sysprocesses

WHERE program_name like '%SQLAgent%'

) AS SYSP

 ON RIGHT(SJV.JOB_ID,12) = SYSP.SYSP_JOB_ID_RIGHT_12

WHERE SJA.run_Requested_date is not null 

  AND SJA.stop_execution_date is null

  AND SJV.enabled = 1

ORDER BY

SYSP.SYSP_RUNNING_TIME_SEC DESC

;

/* --Case for SQL Server 2000

SELECT SJV.NAME AS JOB_NAME

, SJV.JOB_ID AS JOB_ID

, SJV.ORIGINATING_SERVER AS JOB_ORIGINATING_SERVER

, SYSP.SYSP_JOB_START_TIME AS JOB_RUN_REQUESTED_DATE

, SYSP.SYSP_SPID

, SYSP.SYSP_WAIT_TIME_MS

, SYSP.SYSP_CPU_TIME_MS

, SYSP.SYSP_PHY_IO

, SYSP.SYSP_THREAD_STAT

, SYSP.SYSP_CMD_NOW

, SYSP.SYSP_LOGIN_NAME

, SYSP.SYSP_RUNNING_TIME_SEC

 FROM msdb.dbo.sysjobs_view AS SJV 

 LEFT JOIN ( SELECT RIGHT(SUBSTRING(program_name,32,32),12) AS SYSP_JOB_ID_RIGHT_12

--, program_name

, spid AS SYSP_SPID

, waittime AS SYSP_WAIT_TIME_MS

, cpu AS SYSP_CPU_TIME_MS

, physical_io AS SYSP_PHY_IO

, status AS SYSP_THREAD_STAT

, cmd AS SYSP_CMD_NOW

, loginame AS SYSP_LOGIN_NAME

, datediff(SECOND,login_time,getdate()) AS SYSP_RUNNING_TIME_SEC

, login_time AS SYSP_JOB_START_TIME

 FROM master..sysprocesses

WHERE program_name like '%SQLAgent%'

) AS SYSP

 ON RIGHT(SJV.JOB_ID,12) = SYSP.SYSP_JOB_ID_RIGHT_12

WHERE SJV.enabled = 1

ORDER BY

SYSP.SYSP_RUNNING_TIME_SEC DESC

;

*/

END

GO

--mark for systemObject

EXEC sys.sp_ms_marksystemobject 'sp_job'

GO

--test

EXEC dbo.sp_job