Thursday, April 22, 2010

Jobs Running on Server

To find out jobs running on a server through T-Sql

DECLARE @job_owner VARCHAR(100);

SELECT @job_owner = SUSER_SNAME()

IF EXISTS
(
    SELECT 1    FROM tempdb.dbo.sysobjects
    WHERE ID = OBJECT_ID(N'tempdb..#JobStats')
)
BEGIN
    DROP TABLE #JobStats
END


CREATE TABLE
    #JobStats
    (
        job_id UNIQUEIDENTIFIER
    ,    last_run_date INT
    ,    last_run_time INT
    ,    next_run_date INT
    ,    next_run_time INT
    ,    next_run_schedule_id  INT
    ,    requested_to_run INT
    ,    request_source INT
    ,    request_source_id VARCHAR(100)
    ,    running INT
    ,    current_step INT
    ,    current_retry_attempt INT
    ,    job_state INT
    )  


 INSERT INTO #JobStats
            EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner
          
SELECT
    sysjobs.name
,    JobStats.current_step
,    JobStats.running
,    JobStats.*
FROM
    #JobStats JobStats
    JOIN MSDB..sysjobs sysjobs
    ON JobStats.job_id = sysjobs.job_id


OR 

SELECT
SYSJOBS.Name
, SYSJOBS.Job_Id
, SYSPROCESSES.HostName
, SYSPROCESSES.LogiName
, *
FROM
MSDB.dbo.SYSJOBS
JOIN
MASTER.dbo.SYSPROCESSES
ON
SUBSTRING(SYSPROCESSES.PROGRAM_NAME,30,34)
=MASTER.dbo.fn_varbintohexstr ( SYSJOBS.job_id)
AND
LEFT(PROGRAM_NAME,28) ='SQLAgent - TSQL JobStep (Job'

0 comments:

Post a Comment