Identifying SQL Agent Job Name based on the job id.

  • I'm trying to figure out which Job is running, based on this the following name. Where can I find this information?

    SQLAgent - TSQL JobStep (Job 0xFB668E27919DA3489E3DD97061F25B31 : Step 1)

  • Looking in the following msdb tables is usually a good place to start:

    msdb.dbo.sysjobs

    msdb.dbo.sysjobsteps

    [font="Times New Roman"]There's no kill switch on awesome![/font]
  • I've tried those tables, but neither of them contained the following string.

    0xFB668E27919DA3489E3DD97061F25B31

  • Anyone know how to get the job name, any help would be very welcome.

  • The function below will return the job_id when you pass in the string with the "Job 0x...." (I've since discovered a perhaps better way, but can't find that code right now).

    You can use msdb.dbo.sysjobs to translate the job_id to a job_name.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE FUNCTION [dbo].[GetJobIdFromProgramName] (

    @program_name nvarchar(128)

    )

    RETURNS uniqueidentifier

    AS

    BEGIN

    DECLARE @start_of_job_id int

    SET @start_of_job_id = CHARINDEX('(Job 0x', @program_name) + 7

    RETURN CASE WHEN @start_of_job_id > 0 THEN CAST(

    SUBSTRING(@program_name, @start_of_job_id + 06, 2) + SUBSTRING(@program_name, @start_of_job_id + 04, 2) +

    SUBSTRING(@program_name, @start_of_job_id + 02, 2) + SUBSTRING(@program_name, @start_of_job_id + 00, 2) + '-' +

    SUBSTRING(@program_name, @start_of_job_id + 10, 2) + SUBSTRING(@program_name, @start_of_job_id + 08, 2) + '-' +

    SUBSTRING(@program_name, @start_of_job_id + 14, 2) + SUBSTRING(@program_name, @start_of_job_id + 12, 2) + '-' +

    SUBSTRING(@program_name, @start_of_job_id + 16, 4) + '-' +

    SUBSTRING(@program_name, @start_of_job_id + 20,12) AS uniqueidentifier)

    ELSE NULL

    END

    END --FUNCTION

    Sample usage:

    SELECT *

    FROM msdb.dbo.sysjobs

    WHERE

    job_id = dbo.GetJobIdFromProgramName ('SQLAgent - TSQL JobStep (Job 0xFB668E27919DA3489E3DD97061F25B31 : Step 1) ')

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • HI

    This is what I use

    /* The job name can be found from this by using a query like below:

    Note there are no quotes or anything else around that job_id value.

    The "0x" in front of the job_id tells SQL Server to convert it to a hex value which then matches with the sysjobs table.

    */

    select * from msdb..sysjobs where job_id = 0xA8CAE08BDBE59E4FBA9EAD9050472F08

  • Hello,

    I guess what Grasshopper said is right but you might got confuse in a type of data it displays.

    Lets have an example

    You are searching based on Binary value of Job ID and the table stores data in uniqueidentifier mode.

    which should be giving output in-spite of any value in any datatype you pass.

    If you run below scripts you might get an idea

    Select CONVERT(binary(16), job_id) FROM msdb.dbo.sysjobs

    Select Job_Id FROM msdb.dbo.sysjobs

    I hope it helps.

    happy sqlying

  • I have just been working on this annoying issues and found several solutions:

    -- convert job_id from sysjobs table to get the job

    select * from msdb.dbo.sysjobs where CONVERT(binary(16), job_id)=0x5C0B2682FA10FE46983970CC3F0E2F68

    -- Below is converting the other way around where binary value is a string:

    select * from msdb.dbo.sysjobs where job_id = Cast(Convert(binary(16), '0x5C0B2682FA10FE46983970CC3F0E2F68', 1) as uniqueidentifier)

    -- translate the program name string back to uniqueidentifier:

    declare @program_name nvarchar(128) = 'SQLAgent - TSQL JobStep (Job 0x5C0B2682FA10FE46983970CC3F0E2F68 : Step 3)'

    DECLARE @start_of_job_id int = CHARINDEX('(Job 0x', @program_name) + 5

    declare @jobidFromString nvarchar(64)

    select @jobidFromString = Substring(@program_name, @start_of_job_id, 34)

    select Cast(Convert(binary(16), @jobidFromString, 1) as uniqueidentifier)

    -- below is an example how you can use above in a query when you actually have to join tables:

    select top 1000 tc.Name as EventCategory, te.Name as EventName

    , sj.name as JobName

    , logs.*

    from tblLogTraceLoginLogout as logs

    inner join sys.trace_events as te

    on logs.EventClass = te.trace_event_id

    inner join sys.trace_categories as tc

    on te.category_id = tc.category_id

    left join msdb.dbo.sysjobs as sj

    on

    case when logs.ApplicationName like '%Job 0x%' then

    Cast(Convert(binary(16), Substring(logs.ApplicationName, CHARINDEX('(Job 0x', logs.ApplicationName)+5, 34), 1) as uniqueidentifier)

    else null end = sj.job_id

    Not the most beautiful join you have ever seen, but it works! 🙂

  • This is an old post, which I came across when looking to find which job was running. I ended up creating the following script:

    http://www.rbvandenberg.com/t-sql/running-processes-including-job-step-name/

     

     

    Robert van den Berg

    Freelance DBA
    Author of:

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply