Check status of SQL Jobs

Finding out the status of SQL Jobs is a simple task which can be accomplished via the GUI or in code using EXEC msdb.dbo.sp_help_job.

However one issue that is encountered is that the results from this procedure can not easily be used. If an attempt is made to insert the results into another table an error is thrown.

“An INSERT EXEC statement cannot be nested.”

CREATE TABLE #Results
(
[job_id] UNIQUEIDENTIFIER,
[originating_server] NVARCHAR(256),
[name] SYSNAME,
[enabled] TINYINT,
[description] NVARCHAR(1024),
[start_step_id] INT,
[category] SYSNAME,
[owner] SYSNAME,
[notify_level_eventlog] INT,
[notify_level_email] INT,
[notify_level_netsend] INT,
[notify_level_page] INT,
[notify_email_operator] NVARCHAR(MAX),
[notify_netsend_operator] NVARCHAR(MAX),
[notify_page_operator] NVARCHAR(MAX),
[delete_level] INT,
[date_created] DATETIME,
[date_modified] DATETIME,
[version_number] INT,
[last_run_date] INT,
[last_run_time] INT,
[last_run_outcome] INT,
[next_run_date] INT,
[next_run_time] INT,
[next_run_schedule_id] INT,
[current_execution_status] INT,
[current_execution_step] SYSNAME,
[current_retry_attempt] INT,
[has_step] INT,
[has_schedule] INT,
[has_target] INT,
[type] INT
)

INSERT INTO #Results
EXEC msdb.dbo.sp_help_job
--Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
--An INSERT EXEC statement cannot be nested.

Workarounds

In order to capture the data there are several options.

0. Perhaps Microsoft will refactor the code and create a management view/tvf to retrieve jobs with status? 😉

1. Use Openrowset and enable “ad hoc distributed queries” = Lazy Option and comes with security issues. There is also further issues with 2012 see links.

http://blogs.msdn.com/b/sqlagent/archive/2012/07/12/workaround-sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error.aspx
https://connect.microsoft.com/SQLServer/feedback/details/737341/sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error

2. Check status via SMO

[Void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|Out-Null;
[Void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")|Out-Null;
$Instance = New-Object "Microsoft.SqlServer.Management.Smo.Server" "localhost";
$Instance.JobServer.Jobs|ft

I suppose you could create a job step to put this data in a table and query that instead.

Note it is NOT possible to create a SQL CLR and use SMO as you will get one of the following errors:

System.Security.SecurityException: That assembly does not allow partially trusted callers.
System.Exception: This functionality is disabled in the SQLCLR. It is recommended that you execute from your client application.

3. Use master.dbo.xp_sqlagent_enum_jobs instead which is called as part of EXEC msdb.dbo.sp_help_job.

--http://jongurgul.com/blog/check-status-of-sql-jobs
DECLARE @t TABLE
(
[Job ID] UNIQUEIDENTIFIER,[Last Run Date] CHAR(8),[Last Run Time] CHAR(6),[Next Run Date] CHAR(8),[Next Run Time] CHAR(6),[Next Run Schedule ID] INT,
[Requested To Run] INT,[Request Source] INT,[Request Source ID] SQL_VARIANT,[Running] INT,[Current Step] INT,[Current Retry Attempt] INT,[State] INT
)

INSERT INTO @t
EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs=1,@job_owner='0x4A6F6E47757267756C'

SELECT
 es.[session_id] [SessionID]
,t.[Request Source ID] [Requester]
,t.[Job ID] [JobID]
,sj.[name] [JobName]
,sjs.[step_id] [StepID]
,sjs.[step_name] [StepName]
,CASE t.[State]
 WHEN 0 THEN 'Not idle or suspended'
 WHEN 1 THEN 'Executing'
 WHEN 2 THEN 'Waiting For Thread'
 WHEN 3 THEN 'Between Retries'
 WHEN 4 THEN 'Idle'
 WHEN 5 THEN 'Suspended'
 WHEN 6 THEN 'WaitingForStepToFinish'
 WHEN 7 THEN 'PerformingCompletionActions'
 ELSE ''
 END [State]
,sja.[start_execution_date] [FirstStepStartDate]
,sja.[last_executed_step_id] [LastStepID]
,sja.[last_executed_step_date] [LastStepStartDate]
,sja.[stop_execution_date] [LastStepEndDate]
FROM @t t
INNER JOIN msdb..sysjobs sj ON t.[Job ID] = sj.[job_id]
INNER JOIN msdb..sysjobsteps sjs ON sjs.[job_id] = sj.[job_id]
AND t.[Job ID] = sjs.[job_id]
AND t.[Current Step] = sjs.[step_id]
INNER JOIN
(
	SELECT * FROM msdb..sysjobactivity d
	WHERE EXISTS
	(
	SELECT 1
	FROM msdb..sysjobactivity l
	GROUP BY l.[job_id]
	HAVING l.[job_id] = d.[job_id]
	AND MAX(l.[start_execution_date]) = d.[start_execution_date]
	)
) sja
ON sja.[job_id] = sj.[job_id]
LEFT JOIN (SELECT SUBSTRING([program_name],30,34) p,[session_id] FROM sys.dm_exec_sessions
WHERE [program_name] LIKE 'SQLAgent - TSQL JobStep%') es
ON CAST('' AS XML).value('xs:hexBinary(substring(sql:column("es.p"),3))','VARBINARY(MAX)') = sj.[job_id]

http://social.msdn.microsoft.com/Forums/en-IN/transactsql/thread/831c2dcc-75fc-41ac-943d-6457d9fb2ca9