Finding Job Name from Job ID & More Details about Jobs

You may notice that you get Job ID instead of job name in output of SQL trace or in some log. This long value rarely helps us to understand the actual job name. However this Job ID is the unique identifier for the job with in the server.

You may use below code to identify the actual Job Name from Job ID:

SELECT *

FROM msdb.dbo.sysjobs
WHERE job_id = ''

Example:

Below example is taken from a trace output where Job ID is reported as “0xEE3F000650732D4DB127DF7C7F95ED44”. (Please note that you should replace 0xEE3F000650732D4DB127DF7C7F95ED44 with the Job ID you are working with.)

EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 0xEE3F000650732D4DB127DF7C7F95ED44, @step_id = 2, @sql_message_id = 15457, @sql_severity = 0, @run_status = 1, @run_date = 20100909, @run_time = 113501, @run_duration = 0, @operator_id_emailed = 0, @operator_id_netsent = 0, @operator_id_paged = 0, @retries_attempted = 0, @session_id = 180, @message = N'Executed as user: <> Configuration option ''Agent XPs'' changed from 1 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457). The step succeeded.'

You can find the Job Name whose Job ID is 0xEE3F000650732D4DB127DF7C7F95ED44 using below code:

SELECT *
FROM msdb.dbo.sysjobs
WHERE job_id = '0xEE3F000650732D4DB127DF7C7F95ED44'

Querying msdb.dbo.sysjobs gives you more interesting information about the jobs too. For example, you may use below code to find the improtant facts about jobs like when the job last ran, what was the last execution time of the job etc. You may even use a where clause to further find details about the a specific job with below code:

SELECT [sysjobs].[name] AS N'Job Name',
[sysjobsteps].[step_name] AS 'Step Name',
[sysjobsteps].[command] AS N'Step',
[sysjobsteps].[database_name] AS 'Database Name',
[sysjobsteps].[output_file_name] AS 'Output File',
[sysjobsteps].[last_run_date] AS 'Last Execution Date',
[sysjobsteps].[last_run_time] AS 'Last Execution Time',
[sysjobs].[date_modified] AS 'Job Last Modified',
[sysjobs].[version_number] AS 'Version Number'
FROM [msdb].[dbo].[sysjobsteps]INNER JOIN [msdb].[dbo].[sysjobs]ON [msdb].[dbo].[sysjobsteps].[job_id] = [msdb].[dbo].[sysjobs].[job_id];

Please refer to http://msdn.microsoft.com/en-us/library/ms189817.aspx for more details on msdb.dbo.sysjobs and related information.