Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Answered: Default Trace join to msdb.dbo.sysjobs

    So I've been investigating a recent log growth and have found some interesting stuff in the trace.
    Code:
    SELECT *
    FROM   sys.fn_trace_gettable('...\MSSQL\Log\log.trc', default) As trace
     INNER
      JOIN sys.trace_events
        ON trace_events.trace_event_id = trace.eventclass
    WHERE  trace_events.name IN ('Data File Auto Grow', 'Log File Auto Grow')
    ;
    Turns out that there's a job that has triggered a growth a few times!

    The value of the ApplicationName the trace log is as follows:
    Code:
    SQLAgent - TSQL JobStep (Job 0x50CE687FFE245D45945095F732D11DE6 : Step 2)
    From here I needed to work out which job was causing the problem. Easy when you copy-paste!
    Code:
    SELECT *
    FROM   msdb.dbo.sysjobs
    WHERE  job_id = Cast(0x50CE687FFE245D45945095F732D11DE6 As uniqueidentifier)
    But I thought I'd go one better and try to join the parsed value to sysjobs, but I can't get it to work.
    Code:
    ; WITH x (application_name) AS (
      SELECT 'SQLAgent - TSQL JobStep (Job 0x50CE687FFE245D45945095F732D11DE6 : Step 2)'
    )
    , y AS (
    SELECT application_name
         , SubString(application_name, 30, 34) As job_id
    FROM   x
    )
    SELECT *
    FROM   y
     INNER
      JOIN msdb.dbo.sysjobs
        ON sysjobs.job_id = y.job_id
    ;
    Quote Originally Posted by Error Message
    Msg 8169, Level 16, State 2, Line 2
    Conversion failed when converting from a character string to uniqueidentifier.
    That's because I'm not comparing a binary to binary, but a string to a binary i.e.
    Code:
    SELECT *
    FROM   msdb.dbo.sysjobs
    WHERE  job_id = Cast('0x50CE687FFE245D45945095F732D11DE6' As uniqueidentifier)
    Any ideas on how to resolve?
    George
    Home | Blog

  2. Best Answer
    Posted by gvee

    "I think this is what I'm going to run with:
    Code:
    ; WITH job_steps AS (
      SELECT sysjobs.name As job_name
           , sysjobsteps.step_name
           , sysjobsteps.step_id
           , 'SQLAgent - TSQL JobStep (Job ' + Convert(char(34), Cast(sysjobsteps.job_id As binary(16)), 1) + ' : Step ' + Cast(sysjobsteps.step_id As varchar(11)) + ')' As DummyApplicationName
      FROM   msdb.dbo.sysjobs
       INNER
        JOIN msdb.dbo.sysjobsteps
          ON sysjobsteps.job_id = sysjobs.job_id
    )
    SELECT TOP 5
           *
    FROM   sys.fn_trace_gettable('...\MSSQL\Log\log.trc', default) As trace
     INNER
      JOIN job_steps
        ON job_steps.DummyApplicationName = trace.ApplicationName
    "


  3. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You don't even really have to convert the binary value to uniqueidentifier. At least that's what I found in one of my lazier moments.

    As for your query, it looks like you would be trying to convert everything to a unique identifier, not just the application names from the job records. Try adding a where clause to pick out where application_name like 'SQLAgent - TSQL JobStep%' If you have SSIS jobs, or cmdexec jobs, you may need to play around as needed.

  4. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sadly, I've already tried that

    Here; I've boiled the problem down even more:
    Code:
    ; WITH jobs AS (
      SELECT Cast('7F68CE50-24FE-455D-9450-95F732D11DE6' As uniqueidentifier) As job_id
    )
    , x AS (
      SELECT Cast('0x50CE687FFE245D45945095F732D11DE6' As varchar(max)) As to_match
    )
    SELECT jobs.job_id
         , x.to_match
    FROM   jobs
     FULL
      JOIN x
        ON x.to_match = jobs.job_id
    ;
    Try and get the two values to join together properly!
    George
    Home | Blog

  5. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Best I've managed so far (which is a terrible half-solution at best):
    Code:
    Right(x.to_match, 16) = Right(Replace(Cast(jobs.job_id As varchar(max)), '-', ''), 16)
    Works in the instances I've encountered in my current data set so far, but is not deterministic.
    George
    Home | Blog

  6. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ahh, I am slowly catching up. The string '0x50CE687FFE245D45945095F732D11DE6' has a very different binary representation than the hex value 0x50CE687FFE245D45945095F732D11DE6.

    Try using CONVERT, instead:

    convert(varbinary(max), '0x50CE687FFE245D45945095F732D11DE6', 1)

    The third parameter is the Style parameter. I am not sure is CAST has any style ;-)

  7. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just cracked it:
    Code:
    ; WITH x AS (
    SELECT *
         , CASE WHEN ApplicationName LIKE 'SQLAgent - TSQL JobStep (Job 0x% : Step %)' THEN
             Convert(varchar(max), SubString(ApplicationName, 32, 32))
           END As job_id
         , CASE WHEN ApplicationName LIKE 'SQLAgent - TSQL JobStep (Job 0x% : Step %)' THEN
             Convert(varchar(max), SubString(ApplicationName, 30, 34))
           END As job_id2
    FROM   sys.fn_trace_gettable('...\MSSQL\Log\log.trc', default) As trace
    )
    SELECT TOP 5
           *
    FROM   x
     INNER
      JOIN msdb.dbo.sysjobs
        ON x.job_id = Convert(char(32), Cast(sysjobs.job_id As binary(16)), 2)
    /* or: x.job_id2= Convert(char(34), Cast(sysjobs.job_id As binary(16)), 1) */
    ;
    Ugly, but works.
    Last edited by gvee; 03-09-15 at 12:00. Reason: 2nd method added
    George
    Home | Blog

  8. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think this is what I'm going to run with:
    Code:
    ; WITH job_steps AS (
      SELECT sysjobs.name As job_name
           , sysjobsteps.step_name
           , sysjobsteps.step_id
           , 'SQLAgent - TSQL JobStep (Job ' + Convert(char(34), Cast(sysjobsteps.job_id As binary(16)), 1) + ' : Step ' + Cast(sysjobsteps.step_id As varchar(11)) + ')' As DummyApplicationName
      FROM   msdb.dbo.sysjobs
       INNER
        JOIN msdb.dbo.sysjobsteps
          ON sysjobsteps.job_id = sysjobs.job_id
    )
    SELECT TOP 5
           *
    FROM   sys.fn_trace_gettable('...\MSSQL\Log\log.trc', default) As trace
     INNER
      JOIN job_steps
        ON job_steps.DummyApplicationName = trace.ApplicationName
    Last edited by gvee; 03-09-15 at 12:13. Reason: Improved method
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •