Hi All,
I would like monitor the specific application related job in sql agent if its long running more than 1hr using TSQL script.
Does anyone has any idea on how to implement this because I have to use this monitoring purpose.
Kindly let me know your thoughts/views on the same.
Thanks in advance!!
Best Regards,
SQL server DBA
November 5, 2019 at 3:18 pm
What if the usual run time of the job(s) is more than an hour? Do you want to know about that as well? I generally look for just the jobs which are taking longer to finish, compared to their normal run times. See this article from Thomas LaRock.
https://www.mssqltips.com/sqlservertip/4901/how-to-find-long-running-jobs-in-microsoft-sql-server/
November 5, 2019 at 3:18 pm
What do you want to happen if a long-running job is detected?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 5, 2019 at 3:27 pm
Thanks Sreekanth for your quick response, but that is not exact query I was looking and before posting here I have done some analysis which is not worked for me.
The Job is scheduled run on every 30 seconds and need a simple select command like below(source from https://www.sqlhammer.com/sql-agent-monitoring-long-running-job-queries/) and i was looking for if its long running the job more than 45 get alert.
Kindly let me know suggestions on the same or let me know if you are using any script like this.
SELECT jobs.name AS [Job_Name]
, CONVERT(VARCHAR(23),ja.start_execution_date,121)
AS [Start_execution_date]
, ISNULL(CONVERT(VARCHAR(23),ja.stop_execution_date,121), 'Is Running')
AS [Stop_execution_date]
, CASE ISNULL(CAST(ja.stop_execution_date AS VARCHAR(30)),'NULL')
WHEN 'NULL'
THEN CAST(DATEDIFF(ss,ja.start_execution_date,GETDATE()) AS VARCHAR(30))
ELSE 'Not running'
END AS [Duration_secs]
FROM msdb.dbo.sysjobs jobs
LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = jobs.job_id
AND ja.start_execution_date IS NOT NULL
WHERE jobs.name = 'test'
Best Regards,
SQL server DBA
November 5, 2019 at 3:31 pm
Thanks Phil for your quick response and if its detected that will help for application team to check it further.
even we can avoid any issues from SQL server side
I hope this information helps.
SQL server DBA
November 5, 2019 at 4:35 pm
Thanks Phil for your quick response and if its detected that will help for application team to check it further.
even we can avoid any issues from SQL server side
I hope this information helps.
You have not answered the question, though I think I can guess, based on your answer.
If the SQL Agent job detects a long-running job, you want the job to fail and send e-mail notifications, is that right? And you want to spam people every 30 seconds until the problem is resolved? I don't think that this will be your most popular application.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 5, 2019 at 6:33 pm
No Phil, as i have already mentioned above that if the job is running more than 1hr it should trigger the alert.
SQL server DBA
You need a few mods to your code, in particular to limit the results to only those with the current msdb session_id. As a starting point for you, this code will return results only if the job listed has been running for more than 1 hour:
SELECT jobs.name AS [Job_Name]
, CONVERT(VARCHAR(30),ja.start_execution_date,121)
AS [Start_execution_date]
, ja.stop_execution_date
, [Duration_secs]
FROM msdb.dbo.sysjobs jobs
LEFT JOIN (
SELECT *
FROM msdb.dbo.sysjobactivity
WHERE session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions) AND
start_execution_date IS NOT NULL AND
stop_execution_date IS NULL
) AS ja ON ja.job_id = jobs.job_id
CROSS APPLY (
SELECT DATEDIFF(ss,ja.start_execution_date,GETDATE()) AS [Duration_secs]
) AS ca1
WHERE jobs.name = 'test' AND
Duration_secs > 3600
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 5, 2019 at 7:24 pm
Thank you so much Scott for your help , I have modified few things in the script as below and now we are getting 3 columns in the output(attached screenshot as well.) , Can we do like only Job Name and Duration_secs that would be great help?
Can you help me with this as well?
SELECT jobs.name AS [Job_Name]
, CONVERT(VARCHAR(30),ja.start_execution_date,121)
AS [Start_execution_date]
, [Duration_secs]
FROM msdb.dbo.sysjobs jobs
LEFT JOIN (
SELECT *
FROM msdb.dbo.sysjobactivity
WHERE session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions) AND
start_execution_date IS NOT NULL
) AS ja ON ja.job_id = jobs.job_id
CROSS APPLY (
SELECT DATEDIFF(ss,ja.start_execution_date,GETDATE()) AS [Duration_secs]
) AS ca1
WHERE jobs.name = 'test' AND
Duration_secs > 3600
SQL server DBA
November 5, 2019 at 7:33 pm
Try this, took the above code and added HTML logic, replace with your email and test it....
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @servername SYSNAME = (
SELECT @@SERVERNAME
)
DECLARE @sub NVARCHAR(MAX) = 'Long running job(s) detected on: ' + @servername
SELECT jobs.name AS [Job_Name]
,CONVERT(VARCHAR(30), ja.start_execution_date, 121) AS [Start_execution_date]
--, ja.stop_execution_date
,[Duration_secs]
INTO #MyTempTable
FROM msdb.dbo.sysjobs jobs
LEFT JOIN (
SELECT *
FROM msdb.dbo.sysjobactivity
WHERE session_id = (
SELECT MAX(session_id)
FROM msdb.dbo.syssessions
)
AND start_execution_date IS NOT NULL
AND stop_execution_date IS NULL
) AS ja ON ja.job_id = jobs.job_id
CROSS APPLY (
SELECT DATEDIFF(ss, ja.start_execution_date, GETDATE()) AS [Duration_secs]
) AS ca1
WHERE --jobs.name = 'test' AND
Duration_secs > 3600
IF EXISTS (
SELECT *
FROM #MyTempTable
)
BEGIN
SET @xml = CAST((
SELECT Job_Name AS 'td'
,''
,Start_execution_date AS 'td'
,''
,Duration_secs AS 'td'
,''
FROM #MyTempTable
FOR XML PATH('tr')
,ELEMENTS
) AS NVARCHAR(MAX))
SET @body = '<style>
TH, TD, Body{font-family: Arial;font-size:10pt;}
TH{Color: White}</style>
<html><body>Job(s) running for more than 60 mins
<table border = 1>
<tr>
<TH bgcolor= #0000a0> Job_Name </TH>
<TH bgcolor= #0000a0> Start_execution_date </TH>
<TH bgcolor= #0000a0> Duration_secs</tr>'
SET @body = @body + @xml + '</table></body></html>'
EXEC msdb..sp_send_dbmail @recipients = 'replace_with.your@email.com'
,@body = @body
,@subject = @sub
,@body_format = 'HTML'
END
SELECT *
FROM #MyTempTable
DROP TABLE #MyTempTable
November 5, 2019 at 7:42 pm
Thank you Sreekanth, I believe Scott also mentioned same script apart from HTML code.
However , I have mentioned above that if possible can we write TSQL script like only Job Name and Duration_secs that would be great help?
Thanks in advance!!
SQL server DBA
November 5, 2019 at 8:02 pm
Okay, just remove the unwanted column from above script.
November 5, 2019 at 8:35 pm
No Phil, as i have already mentioned above that if the job is running more than 1hr it should trigger the alert.
Am I missing something obvious here?
The Job is scheduled run on every 30 seconds and need a simple select command ...
The job executes at 1.00.00, finds a long-running job and triggers an alert
The job executes at 1.00.30, finds the same long-running job and (presumably?) triggers the alert again ... unless you take steps to avoid that? How are you avoiding it?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 5, 2019 at 8:57 pm
Sure, easy to list only those two columns. I was just mirroring what your original query had, since I wasn't sure exactly what you needed.
Personally I'd also make the job name a parameter, so it could be passed in, but naturally you can remove that too if you prefer.
DECLARE @job_name nvarchar(128)
SET @job_name = 'test'
SELECT jobs.name AS [Job_Name]
, [Duration_secs]
FROM msdb.dbo.sysjobs jobs
LEFT JOIN (
SELECT *
FROM msdb.dbo.sysjobactivity
WHERE session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions) AND
start_execution_date IS NOT NULL AND
stop_execution_date IS NULL
) AS ja ON ja.job_id = jobs.job_id
CROSS APPLY (
SELECT DATEDIFF(ss,ja.start_execution_date,GETDATE()) AS [Duration_secs]
) AS ca1
WHERE jobs.name = @job_name AND
Duration_secs > 3600
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 6, 2019 at 6:34 pm
Thanks to All.
SQL server DBA
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply