I have a Stored procedure which schedules a job. This Job takes a lot of time to get completed (approx 30 to 40 min). I need to get to know the status of this Job. Below details would help me
1) How to see the list of all jobs that have got scheduled for a future time and are yet to start
2) How to see the the list of jobs running and the time span from when they are running
3) How to see if the job has completed successfully or has stoped in between because of any error.
This question is related to
sql
sql-server-2005
tsql
stored-procedures
;WITH CTE_JobStatus
AS (
SELECT DISTINCT NAME AS [JobName]
,s.step_id
,s.step_name
,CASE
WHEN [Enabled] = 1
THEN 'Enabled'
ELSE 'Disabled'
END [JobStatus]
,CASE
WHEN SJH.run_status = 0
THEN 'Failed'
WHEN SJH.run_status = 1
THEN 'Succeeded'
WHEN SJH.run_status = 2
THEN 'Retry'
WHEN SJH.run_status = 3
THEN 'Cancelled'
WHEN SJH.run_status = 4
THEN 'In Progress'
ELSE 'Unknown'
END [JobOutcome]
,CONVERT(VARCHAR(8), sjh.run_date) [RunDate]
,CONVERT(VARCHAR(8), STUFF(STUFF(CONVERT(TIMESTAMP, RIGHT('000000' + CONVERT(VARCHAR(6), sjh.run_time), 6)), 3, 0, ':'), 6, 0, ':')) RunTime
,RANK() OVER (
PARTITION BY s.step_name ORDER BY sjh.run_date DESC
,sjh.run_time DESC
) AS rn
,SJH.run_status
FROM msdb..SYSJobs sj
INNER JOIN msdb..SYSJobHistory sjh ON sj.job_id = sjh.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON sjh.job_id = s.job_id
AND sjh.step_id = s.step_id
WHERE (sj.NAME LIKE 'JOB NAME')
AND sjh.run_date = CONVERT(CHAR, getdate(), 112)
)
SELECT *
FROM CTE_JobStatus
WHERE rn = 1
AND run_status NOT IN (1,4)
The most simple way I found was to create a stored procedure. Enter the 'JobName' and hit go.
/*-----------------------------------------------------------------------------------------------------------
Document Title: usp_getJobStatus
Purpose: Finds a Current Jobs Run Status
Input Example: EXECUTE usp_getJobStatus 'MyJobName'
-------------------------------------------------------------------------------------------------------------*/
IF OBJECT_ID ( 'usp_getJobStatus','P' ) IS NOT NULL
DROP PROCEDURE usp_getJobStatus;
GO
CREATE PROCEDURE usp_getJobStatus
@JobName NVARCHAR (1000)
AS
IF OBJECT_ID('TempDB..#JobResults','U') IS NOT NULL DROP TABLE #JobResults
CREATE TABLE #JobResults ( 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,
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 #JobResults
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, '';
SELECT job.name AS [Job_Name],
( SELECT MAX(CAST( STUFF(STUFF(CAST(jh.run_date AS VARCHAR),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') AS DATETIME))
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobhistory AS jh
ON jh.job_id = j.job_id AND jh.step_id = 0
WHERE j.[name] LIKE '%' + @JobName + '%'
GROUP BY j.[name] ) AS [Last_Completed_DateTime],
( SELECT TOP 1 start_execution_date
FROM msdb.dbo.sysjobactivity
WHERE job_id = r.job_id
ORDER BY start_execution_date DESC ) AS [Job_Start_DateTime],
CASE
WHEN r.running = 0 THEN
CASE
WHEN jobInfo.lASt_run_outcome = 0 THEN 'Failed'
WHEN jobInfo.lASt_run_outcome = 1 THEN 'Success'
WHEN jobInfo.lASt_run_outcome = 3 THEN 'Canceled'
ELSE 'Unknown'
END
WHEN r.job_state = 0 THEN 'Success'
WHEN r.job_state = 4 THEN 'Success'
WHEN r.job_state = 5 THEN 'Success'
WHEN r.job_state = 1 THEN 'In Progress'
WHEN r.job_state = 2 THEN 'In Progress'
WHEN r.job_state = 3 THEN 'In Progress'
WHEN r.job_state = 7 THEN 'In Progress'
ELSE 'Unknown' END AS [Run_Status_Description]
FROM #JobResults AS r
LEFT OUTER JOIN msdb.dbo.sysjobservers AS jobInfo
ON r.job_id = jobInfo.job_id
INNER JOIN msdb.dbo.sysjobs AS job
ON r.job_id = job.job_id
WHERE job.[enabled] = 1
AND job.name LIKE '%' + @JobName + '%'
I ran into issues on one of my servers querying MSDB tables (aka code listed above) as one of my jobs would come up running, but it was not. There is a system stored procedure that returns the execution status, but one cannot do a insert exec statement without an error. Inside that is another system stored procedure that can be used with an insert exec statement.
INSERT INTO #Job
EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo
And the table to load it into:
CREATE TABLE #Job
(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, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
You haven't specified how would you like to see these details.
For the first sight I would suggest to check Server Management Studio.
You can see the jobs and current statuses in the SQL Server Agent part, under Jobs. If you pick a job, the Property page shows a link to the Job History, where you can see the start and end time, if there any errors, which step caused the error, and so on.
You can specify alerts and notifications to email you or to page you when the job finished successfully or failed.
There is a Job Activity Monitor, but actually I never used it. You can have a try.
If you want to check it via T-SQL, then I don't know how you can do that.
I ran into issues on one of my servers querying MSDB tables (aka code listed above) as one of my jobs would come up running, but it was not. There is a system stored procedure that returns the execution status, but one cannot do a insert exec statement without an error. Inside that is another system stored procedure that can be used with an insert exec statement.
INSERT INTO #Job
EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo
And the table to load it into:
CREATE TABLE #Job
(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, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
Below script gets job status for every job on the server. It also tells how many steps are there and what is the currently running step and elasped time.
SELECT sj.Name,
CASE
WHEN sja.start_execution_date IS NULL THEN 'Never ran'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus',
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then js.StepCount else null end As TotalNumberOfSteps,
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then ISNULL(sja.last_executed_step_id+1,js.StepCount) else null end as currentlyExecutingStep,
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then datediff(minute, sja.run_requested_date, getdate()) ELSE NULL end as ElapsedTime
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
CROSS APPLY (SELECT COUNT(*) FROM msdb.dbo.sysjobsteps as js WHERE js.job_id = sj.job_id) as js(StepCount)
WHERE session_id = (
SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
ORDER BY RunStatus desc
This is an old question, but I just had a similar situation where I needed to check on the status of jobs on SQL Server. A lot of people mentioned the sysjobactivity table and pointed to the MSDN documentation which is great. However, I'd also like to highlight the Job Activity Monitor which provides the status on all jobs that are defined on your server.
I would like to point out that none of the T-SQL on this page will work precisely because none of them join to the syssessions table to get only the current session and therefore could include false positives.
See this for reference: What does it mean to have jobs with a null stop date?
You can also validate this by analyzing the sp_help_jobactivity procedure in msdb.
I realize that this is an old message on SO, but I found this message only partially helpful because of the problem.
SELECT
job.name,
job.job_id,
job.originating_server,
activity.run_requested_date,
DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) as Elapsed
FROM
msdb.dbo.sysjobs_view job
JOIN
msdb.dbo.sysjobactivity activity
ON
job.job_id = activity.job_id
JOIN
msdb.dbo.syssessions sess
ON
sess.session_id = activity.session_id
JOIN
(
SELECT
MAX( agent_start_date ) AS max_agent_start_date
FROM
msdb.dbo.syssessions
) sess_max
ON
sess.agent_start_date = sess_max.max_agent_start_date
WHERE
run_requested_date IS NOT NULL AND stop_execution_date IS NULL
This will show last run status/time or if running, it shows current run time, step number/info, and SPID (if it has associated SPID). It also shows enabled/disabled and job user where it converts to NT SID format for unresolved user accounts.
CREATE TABLE #list_running_SQL_jobs
(
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
, request_source INT NOT NULL
, request_source_id sysname NULL
, running INT NOT NULL
, current_step INT NOT NULL
, current_retry_attempt INT NOT NULL
, job_state INT NOT NULL
);
DECLARE @sqluser NVARCHAR(128)
, @is_sysadmin INT;
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0);
DECLARE read_sysjobs_for_running CURSOR FOR
SELECT DISTINCT SUSER_SNAME(owner_sid)FROM msdb.dbo.sysjobs;
OPEN read_sysjobs_for_running;
FETCH NEXT FROM read_sysjobs_for_running
INTO @sqluser;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #list_running_SQL_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @sqluser;
FETCH NEXT FROM read_sysjobs_for_running
INTO @sqluser;
END;
CLOSE read_sysjobs_for_running;
DEALLOCATE read_sysjobs_for_running;
SELECT j.name
, 'Enbld' = CASE j.enabled
WHEN 0
THEN 'no'
ELSE 'YES'
END
, '#Min' = DATEDIFF(MINUTE, a.start_execution_date, ISNULL(a.stop_execution_date, GETDATE()))
, 'Status' = CASE
WHEN a.start_execution_date IS NOT NULL
AND a.stop_execution_date IS NULL
THEN 'Executing'
WHEN h.run_status = 0
THEN 'FAILED'
WHEN h.run_status = 2
THEN 'Retry'
WHEN h.run_status = 3
THEN 'Canceled'
WHEN h.run_status = 4
THEN 'InProg'
WHEN h.run_status = 1
THEN 'Success'
ELSE 'Idle'
END
, r.current_step
, spid = p.session_id
, owner = ISNULL(SUSER_SNAME(j.owner_sid), 'S-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1))) - CONVERT(BIGINT, 256) * CONVERT(BIGINT, UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1)) / 256)) + '-' + CONVERT(NVARCHAR(12), UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 4), 1)) / 256 + CONVERT(BIGINT, NULLIF(UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1)) / 256, 0)) - CONVERT(BIGINT, UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1)) / 256)) + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 5), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 6), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 6), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 7), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 8), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 8), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 9), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 10), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 10), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 11), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 12), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 12), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 13), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 14), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 14), -1)) * 0), '')) --SHOW as NT SID when unresolved
, a.start_execution_date
, a.stop_execution_date
, t.subsystem
, t.step_name
FROM msdb.dbo.sysjobs j
LEFT OUTER JOIN (SELECT DISTINCT * FROM #list_running_SQL_jobs) r
ON j.job_id = r.job_id
LEFT OUTER JOIN msdb.dbo.sysjobactivity a
ON j.job_id = a.job_id
AND a.start_execution_date IS NOT NULL
--AND a.stop_execution_date IS NULL
AND NOT EXISTS
(
SELECT *
FROM msdb.dbo.sysjobactivity at
WHERE at.job_id = a.job_id
AND at.start_execution_date > a.start_execution_date
)
LEFT OUTER JOIN sys.dm_exec_sessions p
ON p.program_name LIKE 'SQLAgent%0x%'
AND j.job_id = SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 7, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 5, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 3, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 1, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 11, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 9, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 15, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 13, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 17, 4) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 21, 12)
LEFT OUTER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
AND h.instance_id = a.job_history_id
LEFT OUTER JOIN msdb.dbo.sysjobsteps t
ON t.job_id = j.job_id
AND t.step_id = r.current_step
ORDER BY 1;
DROP TABLE #list_running_SQL_jobs;
we can query the msdb in many ways to get the details.
few are
select job.Name, job.job_ID, job.Originating_Server,activity.run_requested_Date,
datediff(minute, activity.run_requested_Date, getdate()) as Elapsed
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity on (job.job_id = activity.job_id)
where run_Requested_date is not null
and stop_execution_date is null
and job.name like 'Your Job Prefix%'
You haven't specified how would you like to see these details.
For the first sight I would suggest to check Server Management Studio.
You can see the jobs and current statuses in the SQL Server Agent part, under Jobs. If you pick a job, the Property page shows a link to the Job History, where you can see the start and end time, if there any errors, which step caused the error, and so on.
You can specify alerts and notifications to email you or to page you when the job finished successfully or failed.
There is a Job Activity Monitor, but actually I never used it. You can have a try.
If you want to check it via T-SQL, then I don't know how you can do that.
This is what I'm using to get the running jobs (principally so I can kill the ones which have probably hung):
SELECT
job.Name, job.job_ID
,job.Originating_Server
,activity.run_requested_Date
,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
FROM
msdb.dbo.sysjobs_view job
INNER JOIN msdb.dbo.sysjobactivity activity
ON (job.job_id = activity.job_id)
WHERE
run_Requested_date is not null
AND stop_execution_date is null
AND job.name like 'Your Job Prefix%'
As Tim said, the MSDN / BOL documentation is reasonably good on the contents of the sysjobsX tables. Just remember they are tables in MSDB.
SELECT sj.name
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj
on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND sj.name = '<your Job Name>'
AND NOT EXISTS( -- make sure this is the most recent run
select 1
from msdb..sysjobactivity new
where new.job_id = aj.job_id
and new.start_execution_date > aj.start_execution_date ) )
print 'running'
Below script gets job status for every job on the server. It also tells how many steps are there and what is the currently running step and elasped time.
SELECT sj.Name,
CASE
WHEN sja.start_execution_date IS NULL THEN 'Never ran'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus',
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then js.StepCount else null end As TotalNumberOfSteps,
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then ISNULL(sja.last_executed_step_id+1,js.StepCount) else null end as currentlyExecutingStep,
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then datediff(minute, sja.run_requested_date, getdate()) ELSE NULL end as ElapsedTime
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
CROSS APPLY (SELECT COUNT(*) FROM msdb.dbo.sysjobsteps as js WHERE js.job_id = sj.job_id) as js(StepCount)
WHERE session_id = (
SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
ORDER BY RunStatus desc
I would like to point out that none of the T-SQL on this page will work precisely because none of them join to the syssessions table to get only the current session and therefore could include false positives.
See this for reference: What does it mean to have jobs with a null stop date?
You can also validate this by analyzing the sp_help_jobactivity procedure in msdb.
I realize that this is an old message on SO, but I found this message only partially helpful because of the problem.
SELECT
job.name,
job.job_id,
job.originating_server,
activity.run_requested_date,
DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) as Elapsed
FROM
msdb.dbo.sysjobs_view job
JOIN
msdb.dbo.sysjobactivity activity
ON
job.job_id = activity.job_id
JOIN
msdb.dbo.syssessions sess
ON
sess.session_id = activity.session_id
JOIN
(
SELECT
MAX( agent_start_date ) AS max_agent_start_date
FROM
msdb.dbo.syssessions
) sess_max
ON
sess.agent_start_date = sess_max.max_agent_start_date
WHERE
run_requested_date IS NOT NULL AND stop_execution_date IS NULL
The tasks above work but I have seen many records in the msdb.dbo.sysjobactivity where run_Requested_date is not null and stop_execution_date is null ---- and the job is not currently running.
I would recommend running the following script to clear out all of the bogus entries (make sure no jobs are running at the time).
SQL2008:
delete activity
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
where
activity.run_Requested_date is not null
and activity.stop_execution_date is null
The most simple way I found was to create a stored procedure. Enter the 'JobName' and hit go.
/*-----------------------------------------------------------------------------------------------------------
Document Title: usp_getJobStatus
Purpose: Finds a Current Jobs Run Status
Input Example: EXECUTE usp_getJobStatus 'MyJobName'
-------------------------------------------------------------------------------------------------------------*/
IF OBJECT_ID ( 'usp_getJobStatus','P' ) IS NOT NULL
DROP PROCEDURE usp_getJobStatus;
GO
CREATE PROCEDURE usp_getJobStatus
@JobName NVARCHAR (1000)
AS
IF OBJECT_ID('TempDB..#JobResults','U') IS NOT NULL DROP TABLE #JobResults
CREATE TABLE #JobResults ( 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,
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 #JobResults
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, '';
SELECT job.name AS [Job_Name],
( SELECT MAX(CAST( STUFF(STUFF(CAST(jh.run_date AS VARCHAR),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') AS DATETIME))
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobhistory AS jh
ON jh.job_id = j.job_id AND jh.step_id = 0
WHERE j.[name] LIKE '%' + @JobName + '%'
GROUP BY j.[name] ) AS [Last_Completed_DateTime],
( SELECT TOP 1 start_execution_date
FROM msdb.dbo.sysjobactivity
WHERE job_id = r.job_id
ORDER BY start_execution_date DESC ) AS [Job_Start_DateTime],
CASE
WHEN r.running = 0 THEN
CASE
WHEN jobInfo.lASt_run_outcome = 0 THEN 'Failed'
WHEN jobInfo.lASt_run_outcome = 1 THEN 'Success'
WHEN jobInfo.lASt_run_outcome = 3 THEN 'Canceled'
ELSE 'Unknown'
END
WHEN r.job_state = 0 THEN 'Success'
WHEN r.job_state = 4 THEN 'Success'
WHEN r.job_state = 5 THEN 'Success'
WHEN r.job_state = 1 THEN 'In Progress'
WHEN r.job_state = 2 THEN 'In Progress'
WHEN r.job_state = 3 THEN 'In Progress'
WHEN r.job_state = 7 THEN 'In Progress'
ELSE 'Unknown' END AS [Run_Status_Description]
FROM #JobResults AS r
LEFT OUTER JOIN msdb.dbo.sysjobservers AS jobInfo
ON r.job_id = jobInfo.job_id
INNER JOIN msdb.dbo.sysjobs AS job
ON r.job_id = job.job_id
WHERE job.[enabled] = 1
AND job.name LIKE '%' + @JobName + '%'
You haven't specified how would you like to see these details.
For the first sight I would suggest to check Server Management Studio.
You can see the jobs and current statuses in the SQL Server Agent part, under Jobs. If you pick a job, the Property page shows a link to the Job History, where you can see the start and end time, if there any errors, which step caused the error, and so on.
You can specify alerts and notifications to email you or to page you when the job finished successfully or failed.
There is a Job Activity Monitor, but actually I never used it. You can have a try.
If you want to check it via T-SQL, then I don't know how you can do that.
-- Microsoft SQL Server 2008 Standard Edition:
IF EXISTS(SELECT 1
FROM msdb.dbo.sysjobs J
JOIN msdb.dbo.sysjobactivity A
ON A.job_id=J.job_id
WHERE J.name=N'Your Job Name'
AND A.run_requested_date IS NOT NULL
AND A.stop_execution_date IS NULL
)
PRINT 'The job is running!'
ELSE
PRINT 'The job is not running.'
SELECT sj.name
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj
on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND sj.name = '<your Job Name>'
AND NOT EXISTS( -- make sure this is the most recent run
select 1
from msdb..sysjobactivity new
where new.job_id = aj.job_id
and new.start_execution_date > aj.start_execution_date ) )
print 'running'
This will show last run status/time or if running, it shows current run time, step number/info, and SPID (if it has associated SPID). It also shows enabled/disabled and job user where it converts to NT SID format for unresolved user accounts.
CREATE TABLE #list_running_SQL_jobs
(
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
, request_source INT NOT NULL
, request_source_id sysname NULL
, running INT NOT NULL
, current_step INT NOT NULL
, current_retry_attempt INT NOT NULL
, job_state INT NOT NULL
);
DECLARE @sqluser NVARCHAR(128)
, @is_sysadmin INT;
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0);
DECLARE read_sysjobs_for_running CURSOR FOR
SELECT DISTINCT SUSER_SNAME(owner_sid)FROM msdb.dbo.sysjobs;
OPEN read_sysjobs_for_running;
FETCH NEXT FROM read_sysjobs_for_running
INTO @sqluser;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #list_running_SQL_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @sqluser;
FETCH NEXT FROM read_sysjobs_for_running
INTO @sqluser;
END;
CLOSE read_sysjobs_for_running;
DEALLOCATE read_sysjobs_for_running;
SELECT j.name
, 'Enbld' = CASE j.enabled
WHEN 0
THEN 'no'
ELSE 'YES'
END
, '#Min' = DATEDIFF(MINUTE, a.start_execution_date, ISNULL(a.stop_execution_date, GETDATE()))
, 'Status' = CASE
WHEN a.start_execution_date IS NOT NULL
AND a.stop_execution_date IS NULL
THEN 'Executing'
WHEN h.run_status = 0
THEN 'FAILED'
WHEN h.run_status = 2
THEN 'Retry'
WHEN h.run_status = 3
THEN 'Canceled'
WHEN h.run_status = 4
THEN 'InProg'
WHEN h.run_status = 1
THEN 'Success'
ELSE 'Idle'
END
, r.current_step
, spid = p.session_id
, owner = ISNULL(SUSER_SNAME(j.owner_sid), 'S-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1))) - CONVERT(BIGINT, 256) * CONVERT(BIGINT, UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1)) / 256)) + '-' + CONVERT(NVARCHAR(12), UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 4), 1)) / 256 + CONVERT(BIGINT, NULLIF(UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1)) / 256, 0)) - CONVERT(BIGINT, UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1)) / 256)) + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 5), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 6), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 6), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 7), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 8), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 8), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 9), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 10), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 10), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 11), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 12), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 12), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 13), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 14), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 14), -1)) * 0), '')) --SHOW as NT SID when unresolved
, a.start_execution_date
, a.stop_execution_date
, t.subsystem
, t.step_name
FROM msdb.dbo.sysjobs j
LEFT OUTER JOIN (SELECT DISTINCT * FROM #list_running_SQL_jobs) r
ON j.job_id = r.job_id
LEFT OUTER JOIN msdb.dbo.sysjobactivity a
ON j.job_id = a.job_id
AND a.start_execution_date IS NOT NULL
--AND a.stop_execution_date IS NULL
AND NOT EXISTS
(
SELECT *
FROM msdb.dbo.sysjobactivity at
WHERE at.job_id = a.job_id
AND at.start_execution_date > a.start_execution_date
)
LEFT OUTER JOIN sys.dm_exec_sessions p
ON p.program_name LIKE 'SQLAgent%0x%'
AND j.job_id = SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 7, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 5, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 3, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 1, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 11, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 9, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 15, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 13, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 17, 4) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 21, 12)
LEFT OUTER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
AND h.instance_id = a.job_history_id
LEFT OUTER JOIN msdb.dbo.sysjobsteps t
ON t.job_id = j.job_id
AND t.step_id = r.current_step
ORDER BY 1;
DROP TABLE #list_running_SQL_jobs;
This is what I'm using to get the running jobs (principally so I can kill the ones which have probably hung):
SELECT
job.Name, job.job_ID
,job.Originating_Server
,activity.run_requested_Date
,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
FROM
msdb.dbo.sysjobs_view job
INNER JOIN msdb.dbo.sysjobactivity activity
ON (job.job_id = activity.job_id)
WHERE
run_Requested_date is not null
AND stop_execution_date is null
AND job.name like 'Your Job Prefix%'
As Tim said, the MSDN / BOL documentation is reasonably good on the contents of the sysjobsX tables. Just remember they are tables in MSDB.
You haven't specified how would you like to see these details.
For the first sight I would suggest to check Server Management Studio.
You can see the jobs and current statuses in the SQL Server Agent part, under Jobs. If you pick a job, the Property page shows a link to the Job History, where you can see the start and end time, if there any errors, which step caused the error, and so on.
You can specify alerts and notifications to email you or to page you when the job finished successfully or failed.
There is a Job Activity Monitor, but actually I never used it. You can have a try.
If you want to check it via T-SQL, then I don't know how you can do that.
-- Microsoft SQL Server 2008 Standard Edition:
IF EXISTS(SELECT 1
FROM msdb.dbo.sysjobs J
JOIN msdb.dbo.sysjobactivity A
ON A.job_id=J.job_id
WHERE J.name=N'Your Job Name'
AND A.run_requested_date IS NOT NULL
AND A.stop_execution_date IS NULL
)
PRINT 'The job is running!'
ELSE
PRINT 'The job is not running.'
;WITH CTE_JobStatus
AS (
SELECT DISTINCT NAME AS [JobName]
,s.step_id
,s.step_name
,CASE
WHEN [Enabled] = 1
THEN 'Enabled'
ELSE 'Disabled'
END [JobStatus]
,CASE
WHEN SJH.run_status = 0
THEN 'Failed'
WHEN SJH.run_status = 1
THEN 'Succeeded'
WHEN SJH.run_status = 2
THEN 'Retry'
WHEN SJH.run_status = 3
THEN 'Cancelled'
WHEN SJH.run_status = 4
THEN 'In Progress'
ELSE 'Unknown'
END [JobOutcome]
,CONVERT(VARCHAR(8), sjh.run_date) [RunDate]
,CONVERT(VARCHAR(8), STUFF(STUFF(CONVERT(TIMESTAMP, RIGHT('000000' + CONVERT(VARCHAR(6), sjh.run_time), 6)), 3, 0, ':'), 6, 0, ':')) RunTime
,RANK() OVER (
PARTITION BY s.step_name ORDER BY sjh.run_date DESC
,sjh.run_time DESC
) AS rn
,SJH.run_status
FROM msdb..SYSJobs sj
INNER JOIN msdb..SYSJobHistory sjh ON sj.job_id = sjh.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON sjh.job_id = s.job_id
AND sjh.step_id = s.step_id
WHERE (sj.NAME LIKE 'JOB NAME')
AND sjh.run_date = CONVERT(CHAR, getdate(), 112)
)
SELECT *
FROM CTE_JobStatus
WHERE rn = 1
AND run_status NOT IN (1,4)
This is an old question, but I just had a similar situation where I needed to check on the status of jobs on SQL Server. A lot of people mentioned the sysjobactivity table and pointed to the MSDN documentation which is great. However, I'd also like to highlight the Job Activity Monitor which provides the status on all jobs that are defined on your server.
we can query the msdb in many ways to get the details.
few are
select job.Name, job.job_ID, job.Originating_Server,activity.run_requested_Date,
datediff(minute, activity.run_requested_Date, getdate()) as Elapsed
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity on (job.job_id = activity.job_id)
where run_Requested_date is not null
and stop_execution_date is null
and job.name like 'Your Job Prefix%'
This is what I'm using to get the running jobs (principally so I can kill the ones which have probably hung):
SELECT
job.Name, job.job_ID
,job.Originating_Server
,activity.run_requested_Date
,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
FROM
msdb.dbo.sysjobs_view job
INNER JOIN msdb.dbo.sysjobactivity activity
ON (job.job_id = activity.job_id)
WHERE
run_Requested_date is not null
AND stop_execution_date is null
AND job.name like 'Your Job Prefix%'
As Tim said, the MSDN / BOL documentation is reasonably good on the contents of the sysjobsX tables. Just remember they are tables in MSDB.
The tasks above work but I have seen many records in the msdb.dbo.sysjobactivity where run_Requested_date is not null and stop_execution_date is null ---- and the job is not currently running.
I would recommend running the following script to clear out all of the bogus entries (make sure no jobs are running at the time).
SQL2008:
delete activity
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
where
activity.run_Requested_date is not null
and activity.stop_execution_date is null
Source: Stackoverflow.com