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:



    [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.


  • 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.





    CREATE FUNCTION [dbo].[GetJobIdFromProgramName] (

    @program_name nvarchar(128)


    RETURNS uniqueidentifier



    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)




    Sample usage:

    SELECT *

    FROM msdb.dbo.sysjobs


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

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

    , 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


    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:



    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