SQL Server Agent - Unique ID for Each Job Execution

Question

I have this query that provides a lot of useful info for my scheduled jobs but there is one thing I am still missing. I am trying to locate a unique ID for every job execution, not the instance or schedule ID. Does this exist, and of so how would I join it to the current tables I'm using? Any assistance is appreciated.

Thanks!

Edit: I know I can create a unique ID for jobs that run once per day but most of the jobs run multiple time per day. Some run every 2 minutes.

            SELECT h.instance_id
                , j.job_id
                ,j.name AS JobName
                ,CASE 
                    WHEN h.step_name = '(Job outcome)'
                        THEN 'Job Run Time'
                    ELSE h.step_name
                    END AS StepName
                ,h.step_id
                ,CAST(STR(h.run_date, 8, 0) AS DATETIME) + CAST(STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS DATETIME) AS StartDatetime
                ,DATEADD(SECOND, ((h.run_duration / 1000000 * 86400 + (h.run_duration - h.run_duration / 1000000 * 1000000) / 10000 * 3600) + (h.run_duration - h.run_duration / 10000 * 10000) / 100 * 60) + (h.run_duration - h.run_duration / 100 * 100), CAST(STR(h.run_date, 8, 0) AS DATETIME) + CAST(STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS DATETIME)) AS EndDatetime
                ,CASE 
                    WHEN STUFF(STUFF(REPLACE(STR(h.run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') > '23:59:00'
                        THEN '23:59:00'
                    ELSE STUFF(STUFF(REPLACE(STR(h.run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':')
                    END AS run_duration_formatted
                ,((h.run_duration / 1000000 * 86400 + (h.run_duration - h.run_duration / 1000000 * 1000000) / 10000 * 3600) + (h.run_duration - h.run_duration / 10000 * 10000) / 100 * 60) + (h.run_duration - h.run_duration / 100 * 100) AS RunDurationInSeconds
                ,CASE h.run_status
                    WHEN 0
                        THEN 'Failed'
                    WHEN 1
                        THEN 'Succeded'
                    WHEN 2
                        THEN 'Retry'
                    WHEN 3
                        THEN 'Cancelled'
                    WHEN 4
                        THEN 'In Progress'
                    END AS ExecutionStatus
            FROM msdb.dbo.sysjobhistory AS h
            INNER JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
            LEFT JOIN [msdb].[dbo].[sysjobactivity] A ON A.job_id = h.job_id
            WHERE (j.enabled = 1)
            AND A.session_id = 1053
            AND (CAST(STR(h.run_date, 8, 0) AS DATETIME) + CAST(STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS DATETIME) >= DATEADD(dd, - 1, CAST(GETDATE() AS DATE)))
                Order by instance_id

S3S · Answer 1 · 2018-07-19T19:29:54.853

Maybe use with dense_rank() and using distinct?

SELECT distinct h.instance_id
                , dense_rank() over (order by j.job_id, h.run_date, h.run_time)
                , j.job_id
                ,j.name AS JobName
                ,CASE 
                    WHEN h.step_name = '(Job outcome)'
                        THEN 'Job Run Time'
                    ELSE h.step_name
                    END AS StepName
                ,h.step_id
                ,CAST(STR(h.run_date, 8, 0) AS DATETIME) + CAST(STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS DATETIME) AS StartDatetime
                ,DATEADD(SECOND, ((h.run_duration / 1000000 * 86400 + (h.run_duration - h.run_duration / 1000000 * 1000000) / 10000 * 3600) + (h.run_duration - h.run_duration / 10000 * 10000) / 100 * 60) + (h.run_duration - h.run_duration / 100 * 100), CAST(STR(h.run_date, 8, 0) AS DATETIME) + CAST(STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS DATETIME)) AS EndDatetime
                ,CASE 
                    WHEN STUFF(STUFF(REPLACE(STR(h.run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') > '23:59:00'
                        THEN '23:59:00'
                    ELSE STUFF(STUFF(REPLACE(STR(h.run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':')
                    END AS run_duration_formatted
                ,((h.run_duration / 1000000 * 86400 + (h.run_duration - h.run_duration / 1000000 * 1000000) / 10000 * 3600) + (h.run_duration - h.run_duration / 10000 * 10000) / 100 * 60) + (h.run_duration - h.run_duration / 100 * 100) AS RunDurationInSeconds
                ,CASE h.run_status
                    WHEN 0
                        THEN 'Failed'
                    WHEN 1
                        THEN 'Succeded'
                    WHEN 2
                        THEN 'Retry'
                    WHEN 3
                        THEN 'Cancelled'
                    WHEN 4
                        THEN 'In Progress'
                    END AS ExecutionStatus
            FROM msdb.dbo.sysjobhistory AS h
            INNER JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
            LEFT JOIN [msdb].[dbo].[sysjobactivity] A ON A.job_id = h.job_id
            WHERE (j.enabled = 1)
            --AND A.session_id = 1053
            AND (CAST(STR(h.run_date, 8, 0) AS DATETIME) + CAST(STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS DATETIME) >= DATEADD(dd, - 1, CAST(GETDATE() AS DATE)))
Order by j.job_id, instance_id

CRN BI · Answer 2 · 2018-07-20T00:09:16.430

Edit: The table I used in this query is populated every 5 minutes with the data from the first query I posted.

OK, I couldn't find a unique ID for each schedule execution so I had to rethink what I could do. The query is below.

This solved my problem. I have a Google chart embedded in SharePoint that lists the jobs and the run times. Each bar represents how long the job took to run. Prior to this solution the chart only showed a failed status if the last step failed. Now with this incorporated I can see if any step failed withing each job execution. This is fantastic! In the image below you can see the red executions had a step failure within the job.

        SELECT D.[instance_id]
            ,[JobName]
            ,[StepName]
            ,[step_id]
            ,[Run_Date]
            ,[StartDatetime]
            ,[StartTime]
            ,[EndDatetime]
            ,[End_Time]
            ,[Run_Duration_Formatted]
            ,[RunDurationInSeconds]
            ,MAX(CASE WHEN C.ExecutionStatus IS NULL THEN D.[ExecutionStatus] ELSE C.ExecutionStatus END) AS ExecutionStatus
            FROM [STG_EDW].[dbo].[Job_Runs_FINAL] AS D
            LEFT JOIN 
            (
            SELECT Step0.Instance_ID
            ,'Failed' AS ExecutionStatus
            FROM(
            SELECT [instance_id]
            ,[JobName]
            ,[StartDatetime]
            ,[EndDatetime]
            FROM [My Table]
            WHERE step_id = 0) AS Step0
            INNER JOIN(
            SELECT Instance_ID, JobName, [StartDatetime], [EndDatetime]
            FROM [My Table]
            WHERE ExecutionStatus = 'Failed') AS B ON Step0.JobName = B.JobName AND B.StartDatetime >= Step0.StartDatetime AND B.EndDatetime <= Step0.EndDatetime
            ) AS C ON D.instance_id = C.instance_id
            Group By  D.[instance_id]
            ,[JobName]
            ,[StepName]
            ,[step_id]
            ,[Run_Date]
            ,[StartDatetime]
            ,[StartTime]
            ,[EndDatetime]
            ,[End_Time]
            ,[Run_Duration_Formatted]
            ,[RunDurationInSeconds]

SQL Server Agent - Unique ID for Each Job Execution

2 Answers2