USE master
GO
IF OBJECT_ID('sp_Jobs') IS NULL EXEC ('CREATE PROCEDURE sp_Jobs AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_Jobs
@NonIdle BIT = 1,
@SelectResults BIT = 1,
@PrintHelp BIT = 1
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @PrintHelp = 1
PRINT 'EXEC dbo.sp_Jobs @NonIdle = 0, @SelectResults = 1, @PrintHelp = 1
-- SELECT * FROM ##sp_Jobs
GO'
DECLARE @is_sysadmin INT = 1,--ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0),
@job_owner SYSNAME = SUSER_SNAME(),
@job_id UNIQUEIDENTIFIER
DECLARE @xp_results TABLE (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id SYSNAME COLLATE database_default
NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs
@is_sysadmin,
@job_owner,
@job_id
IF OBJECT_ID('tempdb..##sp_Jobs') IS NOT NULL
DROP TABLE ##sp_Jobs
CREATE TABLE ##sp_Jobs (JobName SYSNAME NULL,
JobDescription NVARCHAR(512) NULL,
RunningStepName SYSNAME NULL,
RunningStatus VARCHAR(54) NOT NULL,
StartStep INT NULL,
CurrentStep INT NULL,
LastRunTime SMALLDATETIME NULL,
RunningSince VARCHAR(128) NULL,
NextRunTime SMALLDATETIME NULL,
RunningDatabase NVARCHAR(128) NULL,
RunningSubSystem NVARCHAR(40) NULL,
RunningCommandXML XML NULL,
JobEnabled TINYINT NULL,
Notify VARCHAR(11) NULL,
EmailOperator SYSNAME NULL,
LastStepName SYSNAME NULL,
LastStepId INT NULL,
LastStepStatus VARCHAR(9) NULL,
LastStepMessage NVARCHAR(4000) NULL,
LastStepDate SMALLDATETIME NULL,
LastStepRunDur VARCHAR(36) NULL,
LastStepDatabase NVARCHAR(128) NULL,
LastStepSubSystem NVARCHAR(40) NULL,
LastStepCommandXML XML NULL,
LastJobStatus VARCHAR(9) NULL,
LastJobMessage NVARCHAR(4000) NULL,
LastJobDate SMALLDATETIME NULL,
LastJobRunDur VARCHAR(36) NULL,
LastJobOperatorEmailed SYSNAME NULL,
RunningCommand VARCHAR(MAX) NULL,
LastStepCommand VARCHAR(MAX) NULL,
job_id UNIQUEIDENTIFIER NULL)
INSERT ##sp_Jobs
(JobName,
JobDescription,
RunningStepName,
RunningStatus,
StartStep,
CurrentStep,
LastRunTime,
RunningSince,
NextRunTime,
RunningDatabase,
RunningSubSystem,
RunningCommandXML,
JobEnabled,
Notify,
EmailOperator,
LastStepName,
LastStepId,
LastStepStatus,
LastStepMessage,
LastStepDate,
LastStepRunDur,
LastStepDatabase,
LastStepSubSystem,
LastStepCommandXML,
LastJobStatus,
LastJobMessage,
LastJobDate,
LastJobRunDur,
LastJobOperatorEmailed,
RunningCommand,
LastStepCommand,
job_id)
SELECT b.name AS JobName,
NULLIF(b.description, 'No description available.') AS JobDescription,
js.step_name AS RunningStepName,
CASE job_state
WHEN 0 THEN 'Returns only those jobs that are 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 7 THEN 'Performing completion actions'
ELSE 'UNKNOWN'
END AS RunningStatus,
b.start_step_id AS StartStep,
CASE WHEN job_state <> 4 THEN a.current_step
END AS CurrentStep,
CASE WHEN job_state <> 4 THEN LastRunTime
END AS LastRunTime,
CASE WHEN job_state <> 4 THEN RunningSince
END AS RunningSince,
NextRunTime,
NULLIF(js.database_name, '') AS RunningDatabase,
js.SubSystem AS RunningSubSystem,
CASE WHEN RunningCommand <> '' THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + RunningCommand + CHAR (13) AS [Q!1!Q!CDATA]
FOR XML EXPLICIT,
TYPE)
END AS RunningCommandXML,
b.enabled AS JobEnabled,
CASE b.notify_level_email
WHEN 1 THEN 'On Success'
WHEN 2 THEN 'On Failure'
WHEN 3 THEN 'On Complete'
END AS Notify,
op.NAME AS EmailOperator,
h1.LastStepName,
h1.LastStepId,
h1.LastStepStatus,
h1.LastStepMessage,
h1.LastStepDate,
h1.LastStepRunDur,
h1.LastStepDatabase,
h1.LastStepSubSystem,
CASE WHEN h1.LastStepCommand <> '' THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + h1.LastStepCommand + CHAR (13) AS [Q!1!Q!CDATA]
FOR XML EXPLICIT,
TYPE)
END AS LastStepCommandXML,
h2.JobStatus AS LastJobStatus,
h2.JobMessage AS LastJobMessage,
h2.JobDate AS LastJobDate,
h2.JobRunDur AS LastJobRunDur,
h2.OperatorEmailed AS LastJobOperatorEmailed,
RunningCommand,
h1.LastStepCommand,
b.job_id
FROM @xp_results a
LEFT OUTER JOIN msdb.dbo.sysjobs b (NOLOCK) ON a.job_id = b.job_id
LEFT OUTER JOIN msdb.dbo.sysjobsteps js (NOLOCK) ON js.job_id = a.job_id
AND js.step_id = a.current_step
LEFT OUTER JOIN msdb.dbo.sysoperators op (NOLOCK) ON b.notify_email_operator_id > 0
AND op.id = b.notify_email_operator_id
CROSS APPLY (SELECT LTRIM(RTRIM(NULLIF(js.command, ''))) AS RunningCommand,
RIGHT('00000000' + CAST(A.last_run_time AS VARCHAR), 6) AS lrtstr,
RIGHT('00000000' + CAST(A.next_run_time AS VARCHAR), 6) AS nrtstr) t
CROSS APPLY (SELECT CASE WHEN a.last_run_date > 0
AND a.last_run_time > 0
THEN CAST(CAST(a.last_run_date AS VARCHAR) + ' ' + SUBSTRING(lrtstr, 1, 2) + ':' + SUBSTRING(lrtstr, 3, 2) + ':'
+ SUBSTRING(lrtstr, 5, 2) AS SMALLDATETIME)
END AS LastRunTime,
CASE WHEN a.Next_run_date > 0
AND a.Next_run_time > 0
THEN CAST(CAST(a.Next_run_date AS VARCHAR) + ' ' + SUBSTRING(nrtstr, 1, 2) + ':' + SUBSTRING(nrtstr, 3, 2) + ':'
+ SUBSTRING(nrtstr, 5, 2) AS SMALLDATETIME)
END AS NextRunTime) rt
CROSS APPLY (SELECT ISNULL(CAST(NULLIF(DaysDiff, 0) AS VARCHAR) + ' days ', '') + RIGHT('0'
+ CAST(DATEDIFF(MINUTE, ModLastRunTime, GETDATE()) / 60 % 24 AS VARCHAR),
2) + ':' + RIGHT('0'
+ CAST(DATEDIFF(MINUTE, ModLastRunTime,
GETDATE()) % 60 AS VARCHAR), 2)
+ ':' + RIGHT('0' + CAST(DATEDIFF(SECOND, ModLastRunTime, GETDATE()) % 60 AS VARCHAR), 2) AS RunningSince
FROM (SELECT DATEDIFF(HOUR, LastRunTime, GETDATE()) / 27 AS DaysDiff,
DATEADD(DAY, DATEDIFF(DAY, LastRunTime, GETDATE()) - 1, LastRunTime) AS ModLastRunTime) m) rs
OUTER APPLY (SELECT TOP 1
instance_id,
h.MESSAGE AS LastStepMessage,
StepDate AS LastStepDate,
--run_duration AS LastStepRunHHMMSS,
RunDuration AS LastStepRunDur,
CASE h.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
END AS LastStepStatus,
js.step_id AS LastStepId,
js.step_name AS LastStepName,
NULLIF(js.database_name, '') AS LastStepDatabase,
js.SubSystem AS LastStepSubSystem,
LTRIM(RTRIM(NULLIF(js.command, ''))) AS LastStepCommand
FROM msdb.dbo.sysjobhistory h (NOLOCK)
CROSS APPLY (SELECT RIGHT('00000000' + CAST(h.run_time AS VARCHAR), 6) AS lrtst,
RIGHT('00000000' + CAST(run_duration AS VARCHAR), 6) AS RunDur) rs
CROSS APPLY (SELECT CAST(CAST(LEFT(RunDur, LEN(RunDur) - 4) AS INT) AS VARCHAR) + ':' + SUBSTRING(Rundur, LEN(RunDur) - 4 + 1, 2) + ':'
+ RIGHT(RunDur, 2) AS RunDuration,
CASE WHEN h.run_date > 0
AND h.run_time > 0
THEN CAST(CAST(h.run_date AS VARCHAR) + ' ' + SUBSTRING(lrtst, 1, 2) + ':' + SUBSTRING(lrtst, 3, 2) + ':'
+ SUBSTRING(lrtst, 5, 2) AS SMALLDATETIME)
END AS StepDate) sd
LEFT OUTER JOIN msdb.dbo.sysjobsteps js (NOLOCK) ON js.job_id = h.job_id
AND js.step_id = h.step_id
WHERE h.job_id = a.job_id
AND H.step_id > 0
ORDER BY instance_id DESC) h1
OUTER APPLY (SELECT TOP 1
h.MESSAGE AS JobMessage,
JobDate,
run_status,
--run_duration AS JobRunHHMMSS,
RunDuration AS JobRunDur,
so.NAME AS OperatorEmailed,
CASE h.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
END AS JobStatus
FROM msdb.dbo.sysjobhistory h (NOLOCK)
LEFT OUTER JOIN msdb.dbo.sysoperators so (NOLOCK) ON so.id = operator_id_emailed
CROSS APPLY (SELECT RIGHT('00000000' + CAST(h.run_time AS VARCHAR), 6) AS lrtst,
RIGHT('00000000' + CAST(run_duration AS VARCHAR), 6) AS RunDur) l
CROSS APPLY (SELECT CAST(CAST(LEFT(RunDur, LEN(RunDur) - 4) AS INT) AS VARCHAR) + ':' + SUBSTRING(Rundur, LEN(RunDur) - 4 + 1, 2) + ':'
+ RIGHT(RunDur, 2) AS RunDuration,
CASE WHEN h.run_date > 0
AND h.run_time > 0
THEN CAST(CAST(h.run_date AS VARCHAR) + ' ' + SUBSTRING(lrtst, 1, 2) + ':' + SUBSTRING(lrtst, 3, 2) + ':'
+ SUBSTRING(lrtst, 5, 2) AS SMALLDATETIME)
END AS JobDate) sd
WHERE h.job_id = a.job_id
AND h.instance_id > h1.instance_id
ORDER BY instance_id DESC) h2
WHERE @NonIdle = 0
OR (@NonIdle = 1
AND running <> 0)
ORDER BY CASE WHEN job_state = 1 THEN 0
ELSE 1
END,
CASE WHEN H2.run_status <> 1 THEN 0
ELSE 1
END,
b.NAME,
ISNULL(LastRunTime, GETDATE())
IF @SelectResults = 1
SELECT t1.JobName,
t1.JobDescription,
t1.RunningStepName,
t1.RunningStatus,
t1.StartStep,
t1.CurrentStep,
t1.LastRunTime,
t1.RunningSince,
t1.NextRunTime,
t1.RunningDatabase,
t1.RunningSubSystem,
t1.RunningCommandXML,
t1.JobEnabled,
t1.Notify,
t1.EmailOperator,
t1.LastStepName,
t1.LastStepId,
t1.LastStepStatus,
t1.LastStepMessage,
t1.LastStepDate,
t1.LastStepRunDur,
t1.LastStepDatabase,
t1.LastStepSubSystem,
t1.LastStepCommandXML,
t1.LastJobStatus,
t1.LastJobMessage,
t1.LastJobDate,
t1.LastJobRunDur,
t1.LastJobOperatorEmailed,
t1.RunningCommand,
t1.LastStepCommand,
t1.job_id
FROM ##sp_Jobs t1
GO
EXEC sys.sp_ms_marksystemobject sp_Jobs
GO