July 30, 2013 at 6:33 pm
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)
July 31, 2013 at 1:04 am
Looking in the following msdb tables is usually a good place to start:
msdb.dbo.sysjobs
msdb.dbo.sysjobsteps
August 1, 2013 at 5:04 pm
I've tried those tables, but neither of them contained the following string.
0xFB668E27919DA3489E3DD97061F25B31
August 2, 2013 at 12:46 pm
Anyone know how to get the job name, any help would be very welcome.
August 2, 2013 at 2:29 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
July 6, 2015 at 6:23 am
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
July 1, 2016 at 12:38 am
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
November 24, 2016 at 8:06 am
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! 🙂
December 7, 2020 at 12:21 pm
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/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply