SQL Trace: how to replace JobID in ApplicationName column

· Uncategorized
Authors

When you setup a SQL Trace to capture long running transaction, you’ll get a reference to a SQL job that uses the varbinary representation of the JobId like this:

SQLAgent – TSQL JobStep (Job 0x08B976420365AF44B78E40C081D79F18 : Step 6)

Don’t ask me why.

However, you can translate this JobId by its name like this:

SELECT DISTINCT  [ApplicationName] = CASE
      WHEN [ApplicationName] LIKE ‘SQLAgent – TSQL JobStep %’
      THEN (
            SELECT SUBSTRING([ApplicationName],1, CHARINDEX(‘ 0x’, [ApplicationName])) + [name] + SUBSTRING([ApplicationName],CHARINDEX(‘ : ‘, [ApplicationName]), CHARINDEX(‘)’, [ApplicationName], CHARINDEX(‘ : ‘, [ApplicationName]))-CHARINDEX(‘ : ‘, [ApplicationName])) + ‘)’
            FROM [msdb].[dbo].[sysjobs]
            WHERE CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY, job_id), 1) = SUBSTRING([ApplicationName],CHARINDEX(‘0x’, [ApplicationName]), CHARINDEX(‘ ‘, [ApplicationName], CHARINDEX(‘0x’, [ApplicationName]))-CHARINDEX(‘0x’, [ApplicationName])) )
      ELSE [ApplicationName]
      End
FROM fn_trace_gettable(‘C:\Temp\QueryStats.trc’, default)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: