-3

I found this useful query here and I use it to monitor SQL Server jobs.

SELECT
sJobHis.[server],
sJobStep.database_name,
SJob.name,
SJob.enabled,
CASE WHEN SJob.enabled = 0 THEN '0'
WHEN sJobStep.subsystem = 'TSQL' AND sJobStep.command LIKE '%--%' 
AND AVG(CAST(SUBSTRING(STUFF(
            STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':') ,7,2) AS INT)) < 1 THEN '0'
WHEN sJobStep.subsystem = 'TSQL' AND sJobStep.command LIKE '%*/%'
AND AVG(CAST(SUBSTRING(STUFF(
            STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':') ,7,2) AS INT)) < 1
 THEN '0'
ELSE '1' END AS  [ActiveStep],
sLogin.name [JobOwner],
sJobHis.step_id,
sJobHis.step_name,
sJobStep.subsystem AS [CommandType],
sJobStep.command AS [Command],
sJobHis.run_date,
[sJobSch].next_run_time AS [Scheduled_Time],
--sJobHis.run_time,
--sJobHis.run_duration,
AVG( CAST( SUBSTRING(STUFF(
            STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':') ,4,2)AS INT))
       AS [AvgRunDuration_In_Min], 
AVG(CAST(SUBSTRING(STUFF(
            STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':') ,7,2) AS INT))
       AS [AvgRunDuration_In_Sec],
COUNT(*) AS [PerDay]
FROM sysjobs AS [SJob] 
LEFT JOIN sysjobhistory AS [sJobHis] ON SJob.job_id = sJobHis.job_id
LEFT JOIN sysjobsteps AS [sJobStep] ON sJobHis.job_id = sJobStep.job_id AND sJobHis.step_id = sJobStep.step_id
INNER JOIN master.dbo.syslogins [sLogin] ON SJob.owner_sid = sLogin.sid
INNER JOIN dbo.sysjobschedules [sJobSch] ON SJob.job_id = [sJobSch].job_id
WHERE  SJob.[enabled]=0 OR ( sJobHis.step_id > 0  AND (sJobHis.run_date > 20131002 AND sJobHis.run_date < 20131005)) 
GROUP BY sJobHis.[server],
sJobStep.database_name,
SJob.name,
SJob.enabled,
sLogin.name ,
sJobHis.step_id,
sJobHis.step_name,
sJobStep.subsystem ,
sJobStep.command ,
sJobHis.run_date,
[sJobSch].next_run_time
ORDER BY SJob.enabled DESC, SJob.name, sJobHis.run_date DESC

I like to have the big picture of what is going on on my server buy there are two columns which means nothing to me:

enter image description here

I would like to have the columns Scheduled_Time and Running_Time more "human readable" as sometimes I cannot figure out what they are saying.

I sometimes see Scheduled_Time as 92000 or 121200 and I have no idea what that means.

Same problem with Running_Time which sometimes shows 120700 or 20000.

No idea what that means.

I don't need to see the failed jobs

The query I already have is perfect, I just want to format those two columns in a more readable output

2

The script is terrible and the source was...not very authoritative. :/

To be fair to your question, monitoring SQL Server Agent jobs is not as straightforward as one might think, though the human readable time is suspect from the queries' author.

  • The SQL Server Agent Job tables
  • [dbo].[sysjobactivity]

    Records current SQL Server Agent job activity and status.

    dbo.sysjobactivity | Microsoft Docs

  • [dbo].[sysjobhistory]

    Contains information about the execution of scheduled jobs by SQL Server Agent.

    NOTE: Data is updated only after the jobstep completes

    dbo.sysjobhistory | Microsoft Docs

  • [dbo].[sysjobs]

    Stores the information for each scheduled job to be executed by SQL Server Agent.

    dbo.sysjobs | Microsoft Docs

  • [dbo].[sysjobschedules]

    Contains schedule information for jobs to be executed by SQL Server Agent.

    NOTE: The sysjobschedules table refreshes every 20 minutes, which may affect the values returned by the sp_help_jobschedule stored procedure.

    dbo.sysjobschedules | Microsoft Docs

  • [dbo].[sysjobservers]

    Stores the association or relationship of a particular job with one or more target servers

    dbo.sysjobservers | Microsoft Docs

  • [dbo].[sysjobsteps]

    Contains the information for each step in a job to be executed by SQL Server Agent

    dbo.sysjobsteps | Microsoft Docs

  • [dbo].[sysjobstepslogs]

    Contains the job step log for all SQL Server Agent job steps that are configured to write job step output to a table.

    dbo.sysjobstepslogs | Microsoft Docs

  • How to find Failed Jobs

Since the aim is the health of the server, try to focus one the jobs that are actually failing. Focusing on averages is nice, but not very helpful unless you are the app owner and you needed to know these minute details. But even then, challenge the request to find out why.

I stumbled upon an excellent script that lets me find every failed job, the step it failed, and any logs/messages that are reported across our 200+ servers.

USE msdb
GO
/* Create #Temp table to house job events */
IF EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE NAme LIKE '#FailedJobs%')
    DROP TABLE #FailedJobs
CREATE TABLE #FailedJobs (Job VARCHAR(250)
                        , StepFailed VARCHAR(100)
                        , DateRun VARCHAR(30)
                        , TimeRun VARCHAR(30)
                        , Step_name VARCHAR(100)
                        , run_Duration INT
                        , LogOutput VARCHAR(MAX) )

DECLARE @JobName VARCHAR(250)
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE JobsCursor CURSOR FORWARD_ONLY
FOR SELECT Name 
    FROM sysjobs A 
    INNER JOIN dbo.sysjobschedules B ON A.job_id = B.job_id
    WHERE A.enabled = 1
        AND B.next_run_date > 0

    OPEN JobsCursor
    FETCH NEXT FROM JobsCursor INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @job_id = job_id FROM dbo.sysjobs WHERE [name] = @JobName        
    INSERT INTO #FailedJobs (Job, StepFailed, DateRun, TimeRun, Step_name, run_Duration, LogOutput )

    SELECT @JobName AS Job
        , 'Step ' + CAST(JH.step_id AS VARCHAR(3)) 
            + ' of ' + (    SELECT CAST(COUNT(*) AS VARCHAR(5)) 
                        FROM dbo.sysjobsteps 
                        WHERE job_id = @job_id
                        ) AS StepFailed
        , CAST(RIGHT(JH.run_date,2) AS CHAR(2)) 
            + '/' + CAST(SUBSTRING(CAST(JH.run_date AS CHAR(8)),5,2) AS CHAR(2)) 
            + '/' + CAST(LEFT(JH.run_date,4) AS CHAR(4)) AS DateRun
        , LEFT(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)),6),2) 
            + ':' + SUBSTRING(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)),6),3,2) 
            + ':' + LEFT(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)),6),2) AS TimeRun
        , JS.step_name 
        , JH.run_duration 
        , CASE WHEN JSL.[log] IS NULL THEN JH.[Message]
               ELSE JSL.[log]
          END AS LogOutput
    FROM dbo.sysjobsteps JS 
    INNER JOIN dbo.sysjobhistory JH ON JS.job_id = JH.job_id 
                                   AND JS.step_id = JH.step_id 
    LEFT OUTER JOIN dbo.sysjobstepslogs JSL ON JS.step_uid = JSL.step_uid
    WHERE INSTANCE_ID > (SELECT MIN(INSTANCE_ID)
                         FROM (
                                SELECT top (2) INSTANCE_ID, job_id
                                FROM dbo.sysjobhistory
                                WHERE job_id = @job_id
                                AND STEP_ID = 0
                                ORDER BY INSTANCE_ID desc
                                ) A
                        ) 
        AND JS.step_id <> 0 
        AND JH.job_id = @job_id
        /*Status of the job execution:
            0 = Failed
            1 = Succeeded
            2 = Retry
            3 = Canceled
        https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql
        */
        AND JH.run_status = 0
    ORDER BY JS.step_id ASC

    FETCH NEXT FROM JobsCursor INTO @JobName
END
CLOSE JobsCursor
DEALLOCATE JobsCursor

SELECT * 
FROM #FailedJobs

The format that is returned is very insightful: FailedJobExample

Notice how readable this output is. You can not only see which job is failing, but the step_id that it failed on! The LogOutput will return the error, all of this actionable.

Because you probably will want this later on, I added two other queries that are helpful:

1. Find Error Log Location - especially when the above queries are truncated

USE master
GO
EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, NULL, N'asc' 

https://www.mssqltips.com/sqlservertip/2506/identify-location-of-the-sql-server-error-log-file/

2. Parse Job from Program_Name in sys.dm_exec_requests and sys.dm_exec_sessions

Ever wanted to see the sessions that a job is running under and their requests? well now you can easily check this via a few scripts

- PreSolution

SELECT convert(varbinary(32), substring([Program_name], 30, 34), 1) AS Job_ID
     , sjob.name
     , s.*
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
INNER JOIN msdb.dbo.sysjobs sjob 
    ON convert(varbinary(32), substring([Program_name], 30, 34), 1) = sjob.job_id
WHERE program_name like 'SQLAgent - TSQL JobStep %';

ProblemQuery

Just parse through the column Program_Name to retrieve the Job_ID, and make sure you conver it to VARBINARY form. Also note you need to specify the conversion type to 1!

- PostSolution

SolvedQuery

Hopefully this gets you much further ahead and focusing on actionable information.

  • thank you for the time you spent for me. The whole community will be helped by your effort. But I don't want to list the failed job. I want to see all jobs and how much time they took to execute – Francesco Mantovani Sep 14 '17 at 20:39
  • @Francesco.Mantovani please see my script. If you notice at the bottom I pointed out what dbo.sysjobhistory's run_status is for. Remove this as a filter and you can return all the rows. I detailed what those tables are used for. But really...why do you want to know this? How is this actionable? Wouldn't some jobs like your backup operations be more impacting than say, a simple job? Are they all equally important? And if this is for DB health, wouldn't failed jobs be more important? Are you worried of people manually executing these jobs? Too many questions and outside the original question. – clifton_h Sep 14 '17 at 21:10
  • long story short: there are jobs that sometimes take 17 - 23 minutes to execute at night and this slow down our serves. CPU hit 100% and services are nearly down. We need to find the precise job that trig this and precisely for how many minutes it was running. 00.05:32 minutes? 00.17.01 minutes? 00.23.32 minutes? I need precision in seconds – Francesco Mantovani Sep 14 '17 at 21:15
  • clifton_h:I also need a date range from 23/03/2017 to 27/03/2017, that's why I say your script is brilliant but the ugly one get the job done – Francesco Mantovani Sep 14 '17 at 21:22
1

It took 1 year but I have it.

I followed this Microsoft Guide and I was able to have what I wanted: I can now filter job by date, time and even duration.

SELECT
sJobHis.[server],
sJobStep.database_name,
SJob.name,
SJob.enabled,
--CASE WHEN SJob.enabled = 0 THEN '0'
--WHEN sJobStep.subsystem = 'TSQL' AND sJobStep.command LIKE '%--%' 
--AND AVG(CAST(SUBSTRING(STUFF(STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') ,7,2) AS INT)) < 1 THEN '0'
--WHEN sJobStep.subsystem = 'TSQL' AND sJobStep.command LIKE '%*/%'
--AND AVG(CAST(SUBSTRING(STUFF(STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') ,7,2) AS INT)) < 1 THEN '0' ELSE '1' END AS  [ActiveStep],
sLogin.name [JobOwner],
sJobHis.step_id,
sJobHis.step_name,
sJobStep.subsystem AS [CommandType],
sJobStep.command AS [Command],
sJobHis.run_date,
--[sJobSch].next_run_time AS [Scheduled_Time],
STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST([sJobSch].next_run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'Scheduled_Time',
--sJobHis.run_time,
--sJobHis.run_duration,
STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sJobHis.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sJobHis.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)',
--AVG( CAST( SUBSTRING(STUFF(STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') ,4,2)AS INT)) AS [AvgRunDuration_In_Min], 
--AVG(CAST(SUBSTRING(STUFF(STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') ,7,2) AS INT)) AS [AvgRunDuration_In_Sec],
COUNT(*) AS [PerDay]

FROM msdb.dbo.sysjobs AS [SJob] 
LEFT JOIN msdb.dbo.sysjobhistory AS [sJobHis] ON SJob.job_id = sJobHis.job_id
LEFT JOIN msdb.dbo.sysjobsteps AS [sJobStep] ON sJobHis.job_id = sJobStep.job_id AND sJobHis.step_id = sJobStep.step_id
INNER JOIN master.dbo.syslogins [sLogin] ON SJob.owner_sid = sLogin.sid
INNER JOIN msdb.dbo.sysjobschedules [sJobSch] ON SJob.job_id = [sJobSch].job_id
WHERE  SJob.[enabled]=0 OR ( sJobHis.step_id > 0  AND (sJobHis.run_date > 20180825 AND sJobHis.run_date < 20180829)) -- PUT HERE YOUR DATES
GROUP BY sJobHis.[server],
sJobStep.database_name,
SJob.name,
SJob.enabled,
sLogin.name ,
sJobHis.step_id,
sJobHis.step_name,
sJobStep.subsystem ,
sJobStep.command ,
sJobHis.run_date,
sJobHis.run_time,
sJobHis.run_duration,
[sJobSch].next_run_time
ORDER BY SJob.enabled DESC, SJob.name, sJobHis.run_date DESC

I kept the ancient rows so you can see the change I made.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.