SQL Server DBA and Azure SQL Insights: Long Running SQL Agent Jobs

In day-to-day DBA work, one common issue we face is SQL Agent jobs that keep running for hours. They don’t fail, but they block other jobs, eat resources, and usually get noticed only after someone escalates.

To avoid that, I built a simple monitoring script that keeps an eye on long-running SQL Agent jobs and sends an email alert when something crosses the expected time.

-- DROP TABLE DBA.dbo.SqlAgentJobs

-- TRUNCATE TABLE DBA.dbo.SqlAgentJobs

-- alter table DBA.dbo.SqlAgentJobs alter column [Running Since(Hrs)] as datediff(hour,[Running Since],getdate()),

CREATE TABLE DBA.dbo.SqlAgentJobs

( ID INT IDENTITY(1,1),

JobName varchar(255) NOT NULL,

Instance_Id bigint,

[Expected-Max-Duration(Min)] BIGINT,

[Ignore] bit default 0,

[Running Since] datetime2,

--[Running Since(Hrs)] as datediff(hour,[Running Since],getdate()),

[Running Since(Hrs)] as CAST(datediff(MINUTE,[Running Since],getdate())/60 AS numeric(20,1)),

[<3-Hrs] bigint,

[3-Hrs] bigint,

[6-Hrs] bigint,

[9-Hrs] bigint,

[12-Hrs] bigint,

[18-Hrs] bigint,

[24-Hrs] bigint,

[36-Hrs] bigint,

[48-Hrs] bigint,

CollectionTime datetime2 default getdate()

)

--alter table DBA.dbo.SqlAgentJobs alter column [Running Since(Hrs)] as CAST(datediff(MINUTE,[Running Since],getdate())/60 AS numeric(20,1))

--go

INSERT DBA.dbo.SqlAgentJobs

(JobName, [Expected-Max-Duration(Min)],[Ignore])

SELECT [JobName] = j.name,

[Expected-Max-Duration(Min)] = AVG( ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) ),

[Ignore] = (CASE WHEN EXISTS (select v.name as jobname, c.name as category from msdb.dbo.sysjobs_view as v left join msdb.dbo.syscategories as c on c.category_id = v.category_id where c.name like 'repl%' AND v.name = j.name) then 1 else 0 end)

FROM msdb.dbo.sysjobhistory AS h

INNER JOIN msdb.dbo.sysjobs AS j

ON h.job_id = j.job_id

WHERE h.step_id = 0

AND j.name NOT IN (select t.JobName from DBA.dbo.SqlAgentJobs as t)

GROUP BY j.name

ORDER BY [Expected-Max-Duration(Min)] DESC;

-- (98 rows affected)

update dbo.SqlAgentJobs

set [Expected-Max-Duration(Min)] = 180

where Ignore = 0

and [Expected-Max-Duration(Min)] > 180

-- (9 rows affected)

update dbo.SqlAgentJobs

set Ignore = 1

where JobName like 'DBA%'

-- (34 rows affected)

SELECT *

FROM dbo.SqlAgentJobs

where Ignore = 0

--select * FROM dbo.SqlAgentJobs where Ignore = 0;

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#JobPastHistory') IS NOT NULL

DROP TABLE #JobPastHistory;

;WITH t_history AS

(

SELECT j.name AS JobName,

h.instance_id,

h.run_date,

Total_mins =

((run_duration/10000) * 60) +

(run_duration/100 % 100) +

(CASE WHEN run_duration % 100 > 29 THEN 1 ELSE 0 END)

FROM msdb..sysjobs j

INNER JOIN msdb..sysjobhistory h

ON h.job_id = j.job_id

WHERE h.step_id = 0

AND EXISTS

(

SELECT 1

FROM DBA.dbo.SqlAgentJobs b

WHERE b.JobName = j.name

AND b.Ignore = 0

AND h.instance_id > ISNULL(b.instance_id,0)

)

SELECT *,

[TimeRange] =

CASE

WHEN Total_mins/60 >= 48 THEN '48-Hrs'

WHEN Total_mins/60 >= 36 THEN '36-Hrs'

WHEN Total_mins/60 >= 24 THEN '24-Hrs'

WHEN Total_mins/60 >= 18 THEN '18-Hrs'

WHEN Total_mins/60 >= 12 THEN '12-Hrs'

WHEN Total_mins/60 >= 9 THEN '9-Hrs'

WHEN Total_mins/60 >= 6 THEN '6-Hrs'

WHEN Total_mins/60 >= 3 THEN '3-Hrs'

ELSE '<3-Hrs'

END

INTO #JobPastHistory

FROM t_history;

IF OBJECT_ID('tempdb..#JobActivityMonitor') IS NOT NULL

DROP TABLE #JobActivityMonitor;

;WITH t_pivot AS

(

SELECT JobName, [<3-Hrs], [3-Hrs], [6-Hrs], [9-Hrs], [12-Hrs], [18-Hrs],

[24-Hrs], [36-Hrs], [48-Hrs]

FROM

(

SELECT JobName, instance_id, TimeRange

FROM #JobPastHistory

) p

PIVOT

(

COUNT(instance_id)

FOR TimeRange IN

([<3-Hrs], [3-Hrs], [6-Hrs], [9-Hrs], [12-Hrs],

[18-Hrs], [24-Hrs], [36-Hrs], [48-Hrs])

) pvt

t_history_info AS

(

SELECT jp.JobName,

jh.max_instance_id AS instance_id,

[<3-Hrs], [3-Hrs], [6-Hrs], [9-Hrs], [12-Hrs],

[18-Hrs], [24-Hrs], [36-Hrs], [48-Hrs]

FROM t_pivot jp

JOIN

(

SELECT JobName, MAX(instance_id) AS max_instance_id

FROM #JobPastHistory

GROUP BY JobName

) jh ON jp.JobName = jh.JobName

t_jobActivityMonitor AS

(

SELECT

ja.job_id,

j.name AS JobName,

ja.start_execution_date,

ISNULL(ja.last_executed_step_id,0) + 1 AS current_executed_step_id,

js.step_name

FROM msdb.dbo.sysjobactivity ja

JOIN msdb.dbo.sysjobs j

ON ja.job_id = j.job_id

JOIN msdb.dbo.sysjobsteps js

ON ja.job_id = js.job_id

AND ISNULL(ja.last_executed_step_id,0) + 1 = js.step_id

WHERE ja.session_id =

(SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)

AND ja.start_execution_date IS NOT NULL

AND ja.stop_execution_date IS NULL

)

SELECT JobName = COALESCE(a.JobName, h.JobName),

h.instance_id,

[Running Since] = a.start_execution_date,

[<3-Hrs], [3-Hrs], [6-Hrs], [9-Hrs], [12-Hrs],

[18-Hrs], [24-Hrs], [36-Hrs], [48-Hrs]

INTO #JobActivityMonitor

FROM t_jobActivityMonitor a

FULL OUTER JOIN t_history_info h

ON h.JobName = a.JobName;

-- Step 01: Clear previous running jobs

UPDATE DBA.dbo.SqlAgentJobs

SET [Running Since] = NULL

WHERE [Running Since] IS NOT NULL;

-- Step 02: Update current running jobs

UPDATE b

SET [Running Since] = a.[Running Since]

FROM DBA.dbo.SqlAgentJobs b

JOIN #JobActivityMonitor a

ON a.JobName = b.JobName

AND a.[Running Since] IS NOT NULL;

-- Step 03: Update history counters

UPDATE b

SET Instance_Id = a.instance_id,

[<3-Hrs] = ISNULL(b.[<3-Hrs],0) + ISNULL(a.[<3-Hrs],0),

[3-Hrs] = ISNULL(b.[3-Hrs],0) + ISNULL(a.[3-Hrs],0),

[6-Hrs] = ISNULL(b.[6-Hrs],0) + ISNULL(a.[6-Hrs],0),

[9-Hrs] = ISNULL(b.[9-Hrs],0) + ISNULL(a.[9-Hrs],0),

[12-Hrs] = ISNULL(b.[12-Hrs],0) + ISNULL(a.[12-Hrs],0),

[18-Hrs] = ISNULL(b.[18-Hrs],0) + ISNULL(a.[18-Hrs],0),

[24-Hrs] = ISNULL(b.[24-Hrs],0) + ISNULL(a.[24-Hrs],0),

[36-Hrs] = ISNULL(b.[36-Hrs],0) + ISNULL(a.[36-Hrs],0),

[48-Hrs] = ISNULL(b.[48-Hrs],0) + ISNULL(a.[48-Hrs],0)

FROM DBA.dbo.SqlAgentJobs b

JOIN #JobActivityMonitor a

ON a.JobName = b.JobName

AND a.instance_id IS NOT NULL;

-- =========================

-- Email Section

-- =========================

DECLARE @tableHTML NVARCHAR(MAX);

DECLARE @subject VARCHAR(200);

SET @subject = 'Long Running Jobs - ' + @@SERVERNAME;

SET @tableHTML = N'

<style>

#JobActivity { font-family: Trebuchet MS; border-collapse: collapse; width: 100%; }

#JobActivity td, #JobActivity th { border: 1px solid #ddd; padding: 8px; }

#JobActivity tr:nth-child(even){background-color:#f2f2f2;}

#JobActivity th { background-color:#4CAF50; color:white; }

</style>

<H1>Long Running Jobs - Server: ' + @@SERVERNAME + N'</H1>

<thead>

<tr>

<th>JobName</th>

<th>Expected Duration (Min)</th>

<th>Running Since</th>

<th>Running Since (Hrs)</th>

</tr>

</thead>

<tbody>' +

CAST

(

SELECT

td = JobName, '',

td = [Expected-Max-Duration(Min)], '',

td = [Running Since], '',

td = [Running Since(Hrs)], '',

td = ISNULL([<3-Hrs],0), '',

td = ISNULL([3-Hrs],0), '',

td = ISNULL([6-Hrs],0), '',

td = ISNULL([9-Hrs],0), '',

td = ISNULL([12-Hrs],0), '',

td = ISNULL([18-Hrs],0), '',

td = ISNULL([24-Hrs],0), '',

td = ISNULL([36-Hrs],0), '',

td = ISNULL([48-Hrs],0)

FROM DBA.dbo.SqlAgentJobs

WHERE Ignore = 0

AND [Running Since] IS NOT NULL

AND [Running Since(Hrs)] >= 3.0

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX)

) +

N'</tbody></table>

Thanks & Regards,<br>

SQLAlerts<br>

Alert from job [DBA - Long Running Jobs]';

IF @tableHTML IS NOT NULL

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'ajay@sqldbanow.com',

@subject = @subject,

@body = @tableHTML,

@body_format = 'HTML';

END

ELSE

PRINT 'No Long Running job found.';

SELECT *

FROM DBA.dbo.SqlAgentJobs

WHERE Ignore = 0

AND [Running Since] IS NOT NULL;

4 comments:

AnonymousFebruary 8, 2026 at 3:29 PM
New Script
AnonymousFebruary 8, 2026 at 3:40 PM
USE [master]
GO

/****** Object: StoredProcedure [dbo].[SP_Audit_Report] Script Date: 2/8/2026 4:06:27 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[SP_Audit_Report]

as
--Script to generate Prevleged and Active SQL Accounts –

select ''
as PlatformName,
@@SERVERNAME as Server,
'SQL' as PlatformType,
sl.name as Account,
case when sl.isntgroup = 1 then 'GROUP'
when sl.isntgroup = 0 then 'USER'
end as 'Group',
'' as 'Owner',
case when isnull(sl.sysadmin,0) = 1 then 'Yes'
else 'No' end as 'Privileged?',
case when isnull(l.is_disabled,0) = 0 then 'Yes'
else 'No' end as 'Active Account?',
case when isnull(l.is_policy_checked,0) = 1 or sl.isntname = 1 then 'Yes'
else 'No' end as 'Password Required?',
case when isnull(l.is_expiration_checked, 0 ) = 1 or sl.isntname = 1 then ''
else 'Never' end as 'Password Expiration'

FROM syslogins sl
LEFT OUTER JOIN Sys.sql_logins l on sl.sid = l.sid
WHERE sl.name not like '##%' -- Exluding system generated accounts :
-- ##MS_AgentSigningCertificate##
-- ##MS_PolicyEventProcessingLogin##
-- ##MS_PolicySigningCertificate##
-- ##MS_PolicyTsqlExecutionLogin##
-- ##MS_SmoExtendedSigningCertificate##
-- ##MS_SQLAuthenticatorCertificate##
-- ##MS_SQLReplicationSigningCertificate##
-- ##MS_SQLResourceSigningCertificate##
AND sl.name not in ('NT SERVICE\SQLWriter', -- Excluding build in accounts
'NT SERVICE\Winmgmt',
'NT SERVICE\MSSQLSERVER',
'NT AUTHORITY\SYSTEM',
'NT SERVICE\SQLSERVERAGENT')
ORDER BY sl.name

AnonymousFebruary 8, 2026 at 3:41 PM

SELECT
spU.name
,MAX(CASE WHEN srm.role_principal_id = 3 THEN 1 END) AS sysadmin
,MAX(CASE WHEN srm.role_principal_id = 4 THEN 1 END) AS securityadmin
,MAX(CASE WHEN srm.role_principal_id = 5 THEN 1 END) AS serveradmin
,MAX(CASE WHEN srm.role_principal_id = 6 THEN 1 END) AS setupadmin
,MAX(CASE WHEN srm.role_principal_id = 7 THEN 1 END) AS processadmin
,MAX(CASE WHEN srm.role_principal_id = 8 THEN 1 END) AS diskadmin
,MAX(CASE WHEN srm.role_principal_id = 9 THEN 1 END) AS dbcreator
,MAX(CASE WHEN srm.role_principal_id = 10 THEN 1 END) AS bulkadmin
FROM
sys.server_principals AS spR
JOIN
sys.server_role_members AS srm
ON
spR.principal_id = srm.role_principal_id
JOIN
sys.server_principals AS spU
ON
srm.member_principal_id = spU.principal_id
WHERE
spR.[type] = 'R'
GROUP BY
spU.name

---Script to generate Server level Roles & membership for all Logins -
SELECT --sys.server_permissions.class
--, sys.server_permissions.class_desc,
granteeserverprincipal.name AS Login_principal_name
--, sys.server_permissions.type
, sys.server_permissions.permission_name
--, sys.server_permissions.state
, sys.server_permissions.state_desc
--, sys.server_permissions.major_id
-- , sys.server_permissions.minor_id
-- , sys.server_permissions.grantee_principal_id
-- , sys.server_permissions.grantor_principal_id
, granteeserverprincipal.type_desc AS grantee_principal_type_desc
--, grantorserverprinicipal.name AS grantor_name
-- , CASE
-- WHEN sys.server_permissions.state = N'W'
-- THEN N'GRANT'
-- ELSE sys.server_permissions.state_desc
-- END + N' ' + sys.server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' TO ' + QUOTENAME(granteeserverprincipal.name) AS permissionstatement
FROM sys.server_principals AS granteeserverprincipal
INNER JOIN sys.server_permissions
ON sys.server_permissions.grantee_principal_id = granteeserverprincipal.principal_id
INNER JOIN sys.server_principals AS grantorserverprinicipal
ON grantorserverprinicipal.principal_id = sys.server_permissions.grantor_principal_id

where permission_name not in ('Connect SQL','Connect')

AnonymousFebruary 8, 2026 at 3:42 PM
SELECT ''' + @Next + ''', a.name as ''User or Role Name'', a.type_desc as ''Account Type'',
d.permission_name as ''Type of Permission'', d.state_desc as ''State of Permission'',
OBJECT_SCHEMA_NAME(d.major_id) + ''.'' + object_name(d.major_id) as ''Object Name'',
case e.obj_type
when ''AF'' then ''Aggregate function (CLR)''
when ''C'' then ''CHECK constraint''
when ''D'' then ''DEFAULT (constraint or stand-alone)''
when ''F'' then ''FOREIGN KEY constraint''
when ''PK'' then ''PRIMARY KEY constraint''
when ''P'' then ''SQL stored procedure''
when ''PC'' then ''Assembly (CLR) stored procedure''
when ''FN'' then ''SQL scalar function''
when ''FS'' then ''Assembly (CLR) scalar function''
when ''FT'' then ''Assembly (CLR) table-valued function''
when ''R'' then ''Rule (old-style, stand-alone)''
when ''RF'' then ''Replication-filter-procedure''
when ''S'' then ''System base table''
when ''SN'' then ''Synonym''
when ''SQ'' then ''Service queue''
when ''TA'' then ''Assembly (CLR) DML trigger''
when ''TR'' then ''SQL DML trigger''
when ''IF'' then ''SQL inline table-valued function''
when ''TF'' then ''SQL table-valued-function''
when ''U'' then ''Table (user-defined)''
when ''UQ'' then ''UNIQUE constraint''
when ''V'' then ''View''
when ''X'' then ''Extended stored procedure''
when ''IT'' then ''Internal table''
end as ''Object Type''
FROM [' + @Next + '].sys.database_principals a
left join [' + @Next + '].sys.database_permissions d on a.principal_id = d.grantee_principal_id
left join @objects e on d.major_id = e.obj_id
order by a.name, d.class_desc')
delete @dbs where dbname = @Next
select top 1 @Next = dbname from @dbs
end
set nocount off
select * from @permission
--where Action_Type not in ('select','CONNECT REPLICATION','connect','execute','REFERENCES','IMPERSONATE','VIEW DEFINITION','VIEW ANY COLUMN ENCRYPTION KEY DEFINITION','VIEW ANY COLUMN MASTER KEY DEFINITION',
--'VIEW CHANGE TRACKING','VIEW DATABASE STATE')
GO

SQL Server DBA and Azure SQL Insights

Pages

Long Running SQL Agent Jobs - How I Monitor Them Automatically

4 comments:

Report Abuse

My Blog List