SQL Agent Nuggets

SELECT  * FROM sys.dm_server_services WHERE [servicename] LIKE 'SQL Server Agent%'; Check column [status_desc] . It has values that can be displayed. https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-server-services-transact-sql

SSIS package to gather data on running jobs STARTUP sequence container has 2 tasks: GET SQL Agent Session ID ==================== DECLARE @SESSION_ID INT; SELECT @SESSION_ID = MAX(session_id) FROM dbo.syssessions; SET ? = @SESSION_ID; GET Refresh Key ============= DECLARE  @REFRESH_KEY INT; SELECT @REFRESH_KEY = NEXT VALUE FOR [sqlagent].[RefreshKey]; SET ? = @REFRESH_KEY; TRUNCATE TABLE [sqlagent].[STAGE_JobsRunning]; DATA FLOW: STAGE SQL AGENT Jobs Running OLE DB SOURCE: GET SQL AGENT Jobs Running Insert query to get jobs running and associated measures OLE DB DESTINATION: STAGE_JobsRunning EXECUTE SQL: Insert row in Refresh table with RefreshKey and SMALLDATETIME

/* ACTIVE-JOB-AVERAGE-DURATION */ DECLARE @SESSION_ID INT; SELECT @SESSION_ID = MAX(session_id) FROM msdb.dbo.syssessions; ;WITH CTE_JOBS_RUNNING AS ( SELECT j.[name] , a.[job_id] , a.[start_execution_date] , a.[last_executed_step_id] , a.[last_executed_step_date] FROM msdb.dbo.sysjobactivity a JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id WHERE a.session_id = @SESSION_ID AND a.start_execution_date IS NOT NULL AND stop_execution_date IS NULL ) , CTE_JOB_HISTORY AS ( SELECT h.[job_id] , msdb.dbo.agent_datetime(run_date, run_time) AS [start_time] , DATEADD( second , [run_duration] / 10000 * 3600 + -- convert hours to seconds ([run_duration] % 10000) / 100 * 60 + -- convert minutes to seconds ([run_duration] % 10000) % 100 -- get seconds , msdb.dbo.agent_datetime(run_date, run_time)) [end_time] , [run_duration] / 10000 * 3600 + -- convert hours to seconds ([run_duration] % 10000) / 100 * 60 + ...

/* SQL AGENT DATA MART: CREATE_SQL_OBJECTS.sql */ USE dba GO SELECT @@SERVERNAME, DB_NAME() GO IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE [name] = 'sqlagent') BEGIN EXEC sp_executesql @stmt = N'CREATE SCHEMA sqlagent'; END GO IF NOT EXISTS ( SELECT 1  FROM INFORMATION_SCHEMA.TABLES  WHERE [TABLE_SCHEMA] = 'sqlagent' AND [TABLE_NAME] = 'STAGE_JobsRunning' ) BEGIN CREATE TABLE [sqlagent].[STAGE_JobsRunning] ( [RefreshKey] INT, [job_id] uniqueidentifier, [job_name] nvarchar(128), [start_execution_date] datetime, [last_executed_step_id] int, [last_executed_step_date] datetime ) END GO IF NOT EXISTS ( SELECT 1 FROM sys.sequences WHERE [name] = 'RefreshKey' AND [schema_id] = (SELECT [schema_id] FROM sys.schemas WHERE [name] = 'sqlagent') ) BEGIN CREATE SEQUENCE [sqlagent].[RefreshKey] START WITH 1   INCREMENT BY 1;   END GO

 -- Get jobs currently running DECLARE @RUN_DATE DATE = GETDATE(); SELECT j.[name] , a.* FROM msdb.dbo.sysjobactivity a JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id WHERE a.start_execution_date > @RUN_DATE AND stop_execution_date IS NULL ORDER BY 1 -- -- Get any completed steps for jobs currently running jobs -- -- run_status values: -- 0 = Failed -- 1 = Succeeded -- 2 = Retry -- 3 = Canceled -- 4 = In Progress DECLARE  @RUN_DATE DATE = GETDATE() , @RUN_DATE_INT INT = FORMAT(GETDATE(), 'yyyyMMdd'); ;WITH CTE_ACTIVE_JOBS AS ( SELECT a.[job_id] FROM msdb.dbo.sysjobactivity a JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id WHERE a.start_execution_date > @RUN_DATE AND stop_execution_date IS NULL AND last_executed_step_id IS NOT NULL ) SELECT  msdb.dbo.agent_datetime(run_date, run_time) start_time , DATEADD( second , [run_duration] / 10000 * 3600 + -- convert hours to seconds ([run_duration] % 10000) / 100 * 60 + -- conver...

  https://www.sqlservercentral.com/blogs/failing-sql-agent-jobs-part-2