SQL Server Agent Job Activity
SQL Server Agent provides a graphical user interface to inspect the status of agent jobs. The Job Activity Monitor looks like:
However, if you want to programmatically obtain the detail about running jobs, including details like when running jobs started and how long they’ve been running, etc, there is no concise way to do that.
I’ve built a query that can be used to obtain those details, and more. The code obtains details from the agent-related tables, views, and stored procedures in the msdb database, along with details from the session-related system dynamic management views.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 |
/* Shows the progress of any running SQL Agent jobs */ DECLARE @Actions TABLE ( ActionID int , ActionText varchar(50) ); INSERT INTO @Actions (ActionID, ActionText) VALUES (1, 'Quit with success') , (2, 'Quit with failure') , (3, 'Go to next step') , (4, 'Go to step on_success_step_id'); DECLARE @can_see_all_running_jobs int = 1; DECLARE @job_owner sysname = 'sa'; DECLARE @job_id uniqueidentifier; DECLARE @job_states TABLE ( job_state_id int NOT NULL , job_state_desc varchar(30) NOT NULL ); INSERT INTO @job_states (job_state_id, job_state_desc) VALUES (0, 'Not idle or suspended') , (1, 'Executing') , (2, 'Waiting for Thread') , (3, 'Between Retries') , (4, 'Idle') , (5, 'Suspended') , (6, 'Waiting for Step to Finish') , (7, 'Performing Completion Actions'); 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 , 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 @can_see_all_running_jobs , @job_owner , @job_id; SELECT JobName = j.name , states.job_state_desc , LastRunDateTime = CASE WHEN COALESCE(xr.last_run_date, 0) > 0 THEN msdb.dbo.agent_datetime(xr.last_run_date, xr.last_run_time) ELSE NULL END , xr.current_step , step_name = ( SELECT '' + sjs.step_name FROM dbo.sysjobsteps sjs WHERE sjs.job_id = j.job_id AND sjs.step_id = xr.current_step FOR XML PATH ('') ) , SQLStatement = SUBSTRING(t.text , ISNULL(r.statement_start_offset / 2 + 1,0) , CASE WHEN ISNULL(r.statement_end_offset, 0) = -1 THEN LEN(t.text) ELSE ISNULL(r.statement_end_offset / 2, 0) END - ISNULL(r.statement_start_offset / 2, 0)) , [On Success Action] = ASuccess.ActionText , [On Fail Action] = AFail.ActionText , [Blocked By] = r.blocking_session_id , [Estimated Completion] = CASE WHEN r.estimated_completion_time = 0 THEN 'UNKNOWN' ELSE CONVERT(VARCHAR(50), DATEADD(MILLISECOND, r.estimated_completion_time, GETDATE()), 120) END , [Duration in Minutes] = DATEDIFF(MINUTE, r.start_time, GETDATE()) , r.last_wait_type , r.start_time FROM msdb.dbo.sysjobs j INNER JOIN @xp_results xr ON j.job_id = xr.job_id INNER JOIN msdb.dbo.sysjobsteps js ON xr.job_id = js.job_id AND xr.current_step = js.step_id LEFT JOIN @job_states states on xr.job_state = states.job_state_id LEFT JOIN sys.dm_exec_sessions s ON SUBSTRING(s.[program_name],30,34) = master.dbo.fn_varbintohexstr(j.job_id) AND SUBSTRING(s.[program_name], 72, LEN(s.[program_name]) - 72) = xr.current_step LEFT JOIN sys.dm_exec_requests r on s.session_id = r.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t LEFT JOIN @Actions AFail ON js.on_fail_action = AFail.ActionID LEFT JOIN @Actions ASuccess ON js.on_success_action = ASuccess.ActionID WHERE j.enabled = 1 /* enabled jobs only */ AND xr.running = 1 /* running jobs only */ ORDER BY j.name; |
For help deciding when to schedule SQL Server Agent Jobs, you might look for gaps between existing jobs.