Job Start Times

[DanEvans]

Does anyone know how to programatically find the start time of a job that only has one step? The problem is, the job has not written any records to sysjobhistory yet, so no info is availabe as to when it started. xp_enum_jobs will tell you if it is running, but not when it started.

I am writing some code to help capture long running jobs and job steps so they can be addressed while they are running. Some jobs have only one step, so I can't tell when they started until they finished. We have jobs in both SQL Server 2000 and 2005, so cross compatability would be excellent.

Any help you can provide, or alternate approaches to tackling the problem (pretend like I have no software budget) would be much appreciated!



Thanks,
Dan

 

[SQLBill]

Here's what I am using. I use sysprocesses to gather information on the jobs.

Create table tRunningJob
(
HostName VARCHAR(25),
Step INT,
Job VARCHAR(17),
JobDuration int,
Status VARCHAR(10),
LastBatch VARCHAR(25)
)
CREATE TABLE tStuckJob
(
ServerName VARCHAR(30),
JobName VARCHAR(100),
StepNumber INT,
Duration VARCHAR(20)
)

Your Job should have these steps

1. --empty the tables of old data
DELETE tRunningJob
DELETE tStuckJob

2. --gather data on Jobs (this script is the same as pulling specific information from sp_who2)
--for 2000
INSERT INTO tRunningJob
SELECT sj.originating_server AS Server,
       SUBSTRING(REVERSE(RTRIM(program_name)),2,1) AS Step,
       STUFF(REVERSE(SUBSTRING(REVERSE(RTRIM(program_name)),11,16)),5,0,'-') AS Job,
       DATEDIFF(MI, last_batch, GETDATE()) AS JobDuration,
       status,
       last_batch
FROM [servername].master.dbo.sysprocesses sp
  JOIN [servername].msdb.dbo.sysjobs sj
     ON STUFF(REVERSE(SUBSTRING(REVERSE(RTRIM(sp.program_name)),11,16)),5,0,'-') = RIGHT(sj.job_id, 17)
WHERE program_name LIKE 'SQLAgent - TSQL JobStep %'

--for 2005
same script as above, but change master.dbo.sysprocesses to master.sys.sysprocesses

3. --get the jobs that are running too long. Either longer than it did previously or over one hour.
--for 2000 / 2005

INSERT INTO tStuckJob (servername, jobname, stepnumber, duration) 
SELECT [servername], sj.name AS jobname, sjs.step_id,
       CONVERT(VARCHAR(10),(jd.jobduration / 60.0)) + ' Hours' AS Duration
  FROM [servername].msdb.dbo.sysjobs sj
    JOIN [servername].msdb.dbo.sysjobsteps sjs
       ON sj.job_id = sjs.job_id
    JOIN [rptingserver].SQLData.dbo.tRunningJob jd
      ON RIGHT(sjs.job_id, 17) = jd.job
        AND sjs.step_id = jd.step
     WHERE (jd.jobduration > sjs.last_run_duration)
    OR (jd.jobduration / 60.0) > 1

-SQLBill


Posting advice: FAQ481-4875

 

[SQLBill]

I should have explained something else....

We run this script on one server (rptingserver) and have it run against every server we maintain. If you are going to run it on the same server you are checking, don't use the [servername] or [rptingserver] qualifiers.

You will have to make your own adjustment to the final line ( OR (jd.jobduration / 60.0) > 1). I use that line since the previous job may have run too long. If so, then I want to know if it's running longer than one hour. For example, a job normally runs 30 minutes. The last time it ran 1.5 hours and I got an alert. The next time it won't report if it has only been running for 1.25 (that's less than the LAST run duration) - except for that last line which forces the report since it's been over an hour.

-SQLBill

Posting advice: FAQ481-4875

 

[DanEvans]

Thanks for your suggestions, SQLBill. I had a little trouble with your suggestion, but it set me in the right path. Here is what I came up with for sql server 2005. This uses [MSDB].[DBO].[SYSJOBACTIVITY] and msdb.dbo.syssessions, neither of which exist in 2000.

CREATE TABLE #xp_results (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)

  DECLARE @is_sysadmin INT
  DECLARE @job_owner   sysname
  DECLARE @job_id      UNIQUEIDENTIFIER
  DECLARE @CHECKTIME   DATETIME
  -- Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
  SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
  SELECT @job_owner = SUSER_SNAME()
  SET @CHECKTIME = GETDATE()

    INSERT INTO #xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
 
--Delete jobs that aren't running
Delete from #xp_results where running <> 1


 declare @session_id INT 
 SELECT TOP(1) @session_id = session_id FROM msdb.dbo.syssessions ORDER by agent_start_date DESC 

		SELECT J.NAME
			   ,DATEDIFF(MINUTE,ISNULL(LAST_EXECUTED_STEP_DATE,START_EXECUTION_DATE),
						 GETDATE()) AS ELAPSED_MINUTES
		FROM   #XP_RESULTS R
			   INNER JOIN MSDB.DBO.SYSJOBS J
				 ON R.JOB_ID = J.JOB_ID
			   INNER JOIN   [MSDB].[DBO].[SYSJOBACTIVITY] A
				 ON R.JOB_ID = A.JOB_ID
		WHERE    A.SESSION_ID = @session_id
			   AND DATEDIFF(MINUTE,ISNULL(LAST_EXECUTED_STEP_DATE,START_EXECUTION_DATE),
						 GETDATE()) > 60 --More than 60 minutes.

DROP TABLE #XP_RESULTS

D

Thanks,
Dan