CREATE PROC [dbo].[sp_longrunningjobs]
(
@hours INT
)
AS
/***********************************************************
DESCRIPTION:
Shows all SQLAgent jobs that have been running longer than the amount of hours specified in
the parameter.
************************************************************/
DECLARE @report_query VARCHAR(8000);
DECLARE @string VARCHAR(100);
DECLARE @xml NVARCHAR(MAX);
DECLARE @body NVARCHAR(MAX);
DECLARE @currently_running_jobs 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
,-- BOOL
current_step INT NOT NULL
,current_retry_attempt INT NOT NULL
,job_state INT NOT NULL
)
--Capture Jobs currently working
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1
,''
SET @string = 'Long Running SQLAgent Jobs on ' + @@servername
SET @xml = CAST((
SELECT DISTINCT jobs.[name] AS 'td'
,''
,ja.run_requested_date AS 'td'
,''
,DATEDIFF(hour, ja.run_requested_date, getdate()) AS 'td'
FROM @currently_running_jobs AS crj
INNER JOIN msdb..sysjobs AS jobs ON crj.job_id = jobs.job_id
INNER JOIN msdb..sysjobactivity AS ja ON ja.job_id = crj.job_id
WHERE crj.running = 1
AND ja.run_requested_date = (
SELECT max(ja1.run_requested_date)
FROM msdb..sysjobactivity AS ja1
WHERE ja1.job_id = crj.job_id
)
AND DATEDIFF(hour, ja.run_requested_date, getdate()) >= 5--@hours
AND jobs.[name] NOT LIKE ('%Database Consistency%')
ORDER BY jobs.[name] DESC
FOR XML PATH('tr')
,ELEMENTS
) AS NVARCHAR(MAX))
SET @body = '<html><body><H3>The following jobs have been running for more than 5 hours</H3>
<table border = 1>
<tr>
<th> Job Name </th> <th> Job Started </th> <th> Hours Run </th></tr>'
SET @body = @body + @xml + '</table></body></html>'
--check if the there is any data available before sending email.
IF EXISTS (
SELECT 1
FROM @currently_running_jobs AS crj
INNER JOIN msdb..sysjobs AS jobs ON crj.job_id = jobs.job_id
INNER JOIN msdb..sysjobactivity AS ja ON ja.job_id = crj.job_id
WHERE crj.running = 1
AND ja.run_requested_date = (
SELECT max(ja1.run_requested_date)
FROM msdb..sysjobactivity AS ja1
WHERE ja1.job_id = crj.job_id
)
AND DATEDIFF(hour, ja.run_requested_date, getdate()) >= 5
AND jobs.[name] NOT LIKE ('%Database Consistency%')
)
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = 'ksubbareddy.sap@gmail.com'
,@subject = @string
,@body = @body
,@body_format = 'HTML'
,@importance = 'High'
END
ELSE
RETURN (0)
---------------------------------------------------------------------------------------------------
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[On Demand]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[On Demand]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ADM - Long Running Jobs',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[On Demand]',
@owner_login_name=N'sa',
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Long Running Jobs] Script Date: 8/18/2025 6:54:54 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Long Running Jobs',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC sp_longrunningjobs 8',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Long Running Jobs',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160810,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=NULL;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
No comments:
Post a Comment