Hi Guys,
It is very important to monitor the long running SQL jobs in production environments and it is cannot miss task of every SQL DBA.
I use below query to catch the long running jobs.
SQL job is created with this query and scheduled to run every 3hrs. which sends notifications of jobs which are running from more than 30mins. You can make it how ever is required to your environment.
Note: Make sure you create your own operator and use it.
It is very important to monitor the long running SQL jobs in production environments and it is cannot miss task of every SQL DBA.
I use below query to catch the long running jobs.
SQL job is created with this query and scheduled to run every 3hrs. which sends notifications of jobs which are running from more than 30mins. You can make it how ever is required to your environment.
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_LongRunningJobNotification]
@Minutes int
AS
BEGIN
set nocount on
declare @ThresholdMinutes int
select @ThresholdMinutes = case when isnull(@Minutes,0) < 1
then 1
when isnull(@Minutes,0) > 120
then 120
else @Minutes
end
if (
select max(datediff(ss,ja.start_execution_date,getdate()))
from msdb.dbo.sysjobactivity ja,msdb.dbo.sysjobs jm
where 1=1
and datediff(ss,ja.start_execution_date,getdate()) > @ThresholdMinutes*60
and jm.job_id = ja.job_id
and jm.category_id = 0
and jm.enabled = 1
and jm.name != 'BlockingHistoryCapture'
and ja.stop_execution_date is null
and ja.start_execution_date is not null
and not exists( select 'x'
from msdb.dbo.sysjobactivity new
where new.job_id = ja.job_id
and new.start_execution_date > ja.start_execution_date)
) > @ThresholdMinutes*60
begin -- If long running job
create table ##LongRunningJobsTemp
( jobname nvarchar(128) not null,
ExecutionDate varchar(19),
MinutesExecuting varchar(8)
)
insert
into ##LongRunningJobsTemp
select rtrim(substring(jm.name,1,64)) Jobname,
convert(varchar(10),ja.start_execution_date,101)+' '+convert(varchar(8),ja.start_execution_date,114) ExecutionDate,
convert(varchar(8),getdate()-ja.start_execution_date,114) MinutesExecuting
from msdb.dbo.sysjobactivity ja,msdb.dbo.sysjobs jm
where 1=1
and datediff(ss,ja.start_execution_date,getdate()) > @ThresholdMinutes*60
and jm.job_id = ja.job_id
and jm.category_id = 0
and jm.enabled = 1
and jm.name != 'BlockingHistoryCapture'
and ja.stop_execution_date is null
and ja.start_execution_date is not null
and not exists( select 'x'
from msdb.dbo.sysjobactivity new
where new.job_id = ja.job_id
and new.start_execution_date > ja.start_execution_date)
order by jm.name
set nocount off
if (select count(*) from ##LongRunningJobsTemp) > 0
begin -- If temp table has
any rows
DECLARE @tableHTML
NVARCHAR(MAX) ;
SET @tableHTML = '<html><head>' +
'<style>' +
'H1 {margin-left:100px;padding-left:7px;font-size:18pt;font-family:calibre;color:Black}' +
'th
{font-family:calibre;background-color:#DB7093;border: solid black
1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:12pt;}
' +
'td
{font-family:calibre;background-color:#DB7093;border: solid black
1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:12pt;}
' +
'</style>' +
'</head>' +
'<Body>'
+
N'<H1>Long
Running Job Alert!</H1>' +
N'<table
border="1">' +
N'<th>JobName</th>' +
N'<th>ExecutionDate</th>' +
N'<th>MinutesExecuting</th>' +
CAST ( ( SELECT td = lr.Jobname, '',
td
= Lr.ExecutionDate, '',
td = Lr.MinutesExecuting, ''
FROM ##LongRunningJobsTemp
as lr
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
declare @email_address varchar(100)
select @email_address =email_address from dbo.sysoperators where name='LongRunningJob_Notification'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'JobMail',
@subject='ServerName: Long
Running Job Alert!',
@recipients= @email_address,
@body=@tableHTML,
@body_format='HTML'
drop table ##LongRunningJobsTemp
end -- If long running job
end
END
27 comments:
It has been simply incredibly generous with you to provide openly what exactly many individuals would’ve marketed for an eBook to end up making some cash for their end, primarily given that you could have tried it in the event you wanted
full stack developer training in chennai
Some us know all relating to the compelling medium you present powerful steps on this blog and therefore strongly encourage contribution from other ones on this subject while our own child is truly discovering a great deal. Have fun with the remaining portion of the year.
AWS Training in chennai
AWS Training in bangalore
Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you..Keep update more information..
Data Science Training in Chennai
Data science training in bangalore
Data science online training
Data science training in pune
You made such an interesting piece to read, giving every subject enlightenment for us to gain knowledge. Thanks for sharing the such information with us
angularjs Training in chennai
angularjs Training in chennai
angularjs-Training in tambaram
angularjs-Training in sholinganallur
angularjs-Training in velachery
I believe there are many more pleasurable opportunities ahead for individuals that looked at your site.
python interview questions and answers | python tutorialspython training institute in electronic city
This is such a great post, and was thinking much the same myself. Another great update.
Microsoft Azure online training
Selenium online training
Java online training
Java Script online training
Share Point online training
Awesome and very useful blog. A great and very informative post, Keep up the good work!
Data Science Courses in Bangalore
It should be noted that whilst ordering papers for sale at paper writing service, you can get unkind attitude. In case you feel that the bureau is trying to cheat you, don't buy term paper from it.
ExcelR Data science courses in Bangalore
i am for the first time here. I found this board and I in finding It truly helpful & it helped me out a lot. I hope to present something back and help others such as you helped me.
big data course malaysia
I was just browsing through the internet looking for some information and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject. Bookmarked this page, will come back for more.
data science course in singapore
I am looking for and I love to post a comment that "The content of your post is awesome" Great work!
pmp certification malaysia
thanks for sharing this information for us
Artificial Intelligence training in Bangalore
Artificial Intelligence training in BTM
data science with python training in Bangalore
data science with python training in BTM
Blue Prism Training in Bangalore
Blue Prism Training in BTM
MERN StackTraining in Bangalore
MERN Stack Training in BTM
SSC Result 2020 Published Date & Time by ssc result
ssc result 2020
Education Board of Bangladesh.
Many of You Search For SSC Result Kobe Dibe on Internet
as Well as Facebook. The results of Secondary School Certificate
(SSC)—and its equivalent examinations—for 2020 have been published.
SSC & Dakhil Result 2020 Published Date is Very Important For T
he Students Who Attend The SSC Exam 2020.
Awesome Blog. the blog is really Impressive. every concept of this blog is really good.
Data Science Training Course In Chennai | Data Science Training Course In Anna Nagar | Data Science Training Course In OMR | Data Science Training Course In Porur | Data Science Training Course In Tambaram | Data Science Training Course In Velachery
These provided information was really so nice,thanks for giving that post and the more skills to develop after refer that post thnska a lot guys.
Ai & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai
Somebody who need windows key click: www.vanskeys.com to got. here i got a working key on my windows about activator. and after i used my windows works properly. hope it help you.Thanks lot!!
Android Training in Chennai
Android Online Training in Chennai
Android Training in Bangalore
Android Training in Hyderabad
Android Training in Coimbatore
Android Training
Android Online Training
It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...data science courses
Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…
oracle training in chennai
oracle training in velachery
oracle dba training in chennai
oracle dba training in velachery
ccna training in chennai
ccna training in velachery
seo training in chennai
seo training in velachery
Very nice blogs!!! i have to learning for lot of information for this sites…Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing, data scientist course in hyderabad with placement
Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area. data science training in Hyderabad
Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing.
machine learning courses in bangalore
I am truly getting a charge out of perusing your elegantly composed articles. It would seem that you burn through a ton of energy and time on your blog. I have bookmarked it and I am anticipating perusing new articles. Keep doing awesome.
data scientist training and placement
Pleasant data, important and incredible structure, as offer great stuff with smart thoughts and ideas, loads of extraordinary data and motivation, the two of which I need, because of offer such an accommodating data here.
business analytics course in hyderabad
Nice blog and informative content. I am impressed a lot with your blog. Keep up your work in further blogs.
Data Scientist Training in Hyderabad
They're produced by the very best degree developers who will be distinguished for your polo dress creation. You'll find Ron Lauren inside an exclusive array which includes particular classes for men, women.
business analytics training in hyderabad
It is the perfect time to make some plans for the future and it is the time to be happy. I've read this post and if I could I would like to suggest some interesting things or suggestions. Perhaps you could write the next articles referring to this article. I want to read more things about it!
data analytics course in hyderabad
Nice post! This is a very nice blog that I will definitively come back to more times this year! Thanks for informative post.
data analytics courses in hyderabad
Post a Comment