April 17, 2023 at 2:07 pm
Hello,
What is the most effective way to notify the team via the dbmail if some of the dbmaintenance jobs are running for over few hours?
How to track the jobs and notify effectively? If there is any available script/best method available, please share it with me.
Thanks.
April 17, 2023 at 4:15 pm
I am not sure what the "best" way to handle this is, but the approach we took was to set up a second job to watch for running jobs. It checks when the job was scheduled to start, then checks how long it has been running. If it is longer than a hard-coded cutoff (for us 15 minutes), it sends out a notice to the DBA team to investigate.
The script I use is:
DECLARE @bLongJob bit
IF OBJECT_ID('tempdb.dbo.#RunningJobs') IS NOT NULL
DROP TABLE #RunningJobs
CREATE TABLE #RunningJobs (
Job_ID UNIQUEIDENTIFIER,
Last_Run_Date INT,
Last_Run_Time INT,
Next_Run_Date INT,
Next_Run_Time INT,
Next_Run_Schedule_ID INT,
Requested_To_Run INT,
Request_Source INT,
Request_Source_ID VARCHAR(100),
Running INT,
Current_Step INT,
Current_Retry_Attempt INT,
State INT )
INSERT INTO #RunningJobs EXEC master.dbo.xp_sqlagent_enum_jobs 1,garbage
SELECT @bLongJob = CASE WHEN CAST(GETDATE() - CONVERT(VARCHAR,DATEADD(S,(last_run_time/10000)*60*60 /* hours */
+((last_run_time - (last_run_time/10000) * 10000)/100) * 60 /* mins */
+ (last_run_time - (last_run_time/100) * 100) /* secs */,
CONVERT(DATETIME,RTRIM(last_run_date),112)),100) AS DATETIME) > CAST('1900-01-01 00:15:00' AS DATETIME)
THEN 1 ELSE 0 END
FROM #RunningJobs JSR
JOIN msdb.dbo.sysjobs
ON JSR.Job_ID=sysjobs.job_id
WHERE Running=1 -- i.e. still running
AND name LIKE '%' -- put your job lookup here
AND last_run_time > 0
ORDER BY name,next_run_date,next_run_time
DROP TABLE #RunningJobs
IF @bLongJob IS NOT NULL
OR @bLongJob > 0
BEGIN
exec msdb.dbo.sp_send_dbmail
@recipients='email address of DBAs',
@subject='job runs longer than 15 minutes',
@body = 'The job has been running for longer than 15 minutes. You may want to investigate.'
END
The above script is for a single job, so would need to create it one per job you want to monitor OR you would need to tweak the script so that it can capture more data and notify people if needed, but it should be good enough to get started.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 30, 2023 at 11:53 pm
Many thanks, Brian.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply