How to find that job is running?

• Leon . Orlov .

  Ten Centuries

  Points: 1212

  More actions

  October 23, 2012 at 3:30 pm

  #1552091

  For your viewing pleasures... here is another working sample tested on sql 8 - 10.50

  IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'#tmp_enum_jobs') )

  DROP TABLE #tmp_enum_jobs;

  ELSE

  CREATE TABLE #tmp_enum_jobs (

  job_id UNIQUEIDENTIFIER NOT NULL

  , last_run_date NVARCHAR(20) NOT NULL

  , last_run_time NVARCHAR(20) NOT NULL

  , next_run_date NVARCHAR(20) NOT NULL

  , next_run_time NVARCHAR(20) NOT NULL

  , next_run_schedule_id INT NOT NULL

  , requested_to_run INT NOT NULL

  , request_source INT NOT NULL

  , request_source_id NVARCHAR(256) NULL

  , running INT NOT NULL

  , current_step INT NOT NULL

  , current_retry_attempt INT NOT NULL

  , job_state INT NOT NULL );

  DECLARE @job_owner NVARCHAR(256)

  DECLARE @is_sysadmin INT

  SET @is_sysadmin = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0)

  SET @job_owner = SUSER_SNAME()

  INSERT INTO #tmp_enum_jobs

  EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

  UPDATE #tmp_enum_jobs

  SET last_run_time = RIGHT('000000' + last_run_time, 6)

  , next_run_time = RIGHT('000000' + next_run_time, 6);

  SELECT @@SERVERNAME AS Server_Name

  , j.name AS Job_Name

  , j.enabled AS Enabled

  , CASE t.running

  WHEN 1 THEN 'Running'

  ELSE CASE h.run_status

  WHEN 2 THEN 'Inactive'

  WHEN 4 THEN 'Inactive'

  ELSE 'Completed'

  END

  END AS Current_Status

  , COALESCE(t.current_step, 0) AS Current_Step

  , CASE WHEN t.last_run_date > 0

  THEN CONVERT (DATETIME, SUBSTRING(t.last_run_date, 1, 4) + '-'

  + SUBSTRING(t.last_run_date, 5, 2) + '-'

  + SUBSTRING(t.last_run_date, 7, 2) + ' '

  + SUBSTRING(t.last_run_time, 1, 2) + ':'

  + SUBSTRING(t.last_run_time, 3, 2) + ':'

  + SUBSTRING(t.last_run_time, 5, 2) + '.000', 121)

  ELSE NULL

  END AS Last_Run_Time

  , CASE h.run_status

  WHEN 0 THEN 'Fail'

  WHEN 1 THEN 'Success'

  WHEN 2 THEN 'Retry'

  WHEN 3 THEN 'Cancel'

  WHEN 4 THEN 'In progress'

  END AS Last_Run_Outcome

  /*, CASE WHEN h.run_duration > 0

  THEN ( h.run_duration / 1000000 ) * ( 3600 * 24 )

  + ( h.run_duration / 10000 % 100 ) * 3600 + ( h.run_duration

  / 100 % 100 )

  * 60 + ( h.run_duration % 100 )

  ELSE NULL

  END AS Last_Run_Duration */

  , CASE WHEN LEN(h.run_duration) > 6 THEN STUFF(STUFF(h.run_duration,LEN(h.run_duration)-3,0,':'),LEN(h.run_duration),0,':')

  ELSE STUFF(STUFF(REPLACE(STR(h.run_duration,6,0),' ','0'),3,0,':'),6,0,':')

  END AS Last_Run_Duration

  FROM #tmp_enum_jobs t

  LEFT JOIN msdb.dbo.sysjobs j (NOLOCK) ON t.job_id = j.job_id

  LEFT OUTER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id

  LEFT OUTER JOIN msdb.dbo.sysjobhistory h (NOLOCK) ON t.job_id = h.job_id

  AND t.last_run_date = h.run_date

  AND t.last_run_time = h.run_time

  --AND h.step_id = 0

  WHERE t.running = 1

  DROP TABLE #tmp_enum_jobs;

  Cheers,

  ~Leon