Identifying SQL Agent Job Name based on the job id.

  • DVSQL

    Hall of Fame

    Points: 3101

    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)

  • Loundy

    SSCertifiable

    Points: 6080

    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]
  • DVSQL

    Hall of Fame

    Points: 3101

    I've tried those tables, but neither of them contained the following string.

    0xFB668E27919DA3489E3DD97061F25B31

  • DVSQL

    Hall of Fame

    Points: 3101

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

  • ScottPletcher

    SSC Guru

    Points: 98045

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • zeldakr

    SSC Eights!

    Points: 971

    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

  • ravi sonaiya

    SSC Journeyman

    Points: 93

    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

  • mzwarthoed

    SSC Journeyman

    Points: 87

    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! 🙂

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

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