SQL Server Agent

SQL Server Agent Job History

SQL Server Jobs History

Objective:

To find the SQL Server Agent Jobs duration including each step in the job, the below script may be useful. Tested on SQL Server 2016.

Companies SQL Server instance may experience some performance issue due to long running jobs. As such, the primary investigation is to get the duration of jobs from SQL Server Agent. If we identity, any specific jobs running long time, then we may concentrate on that specific job information with a period of time and that history of job duration will give an idea what went wrong. It assists for further investigation.

SELECT 
	SERVERPROPERTY('servername') as [ServerName/SQLInstanceName],
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as [ComputerName/NodeName],
	SERVERPROPERTY('IsClustered') as IsClustered ,
	j.name as 'JobName',
	s.step_id as 'Step', -- This will give RunDateTime and Duration in minutes time in each step of the particular job
	s.step_name as 'StepName',
	msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',  -- This gives Date and Time
	((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) AS 'RunDurationMinutes',
	STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time', -- This gives only the time, i.e. Hours when the job ran
    STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)'
FROM msdb.dbo.sysjobs j
	INNER JOIN msdb.dbo.sysjobsteps s 
ON j.job_id = s.job_id
	INNER JOIN msdb.dbo.sysjobhistory h 
ON s.job_id = h.job_id 
	AND s.step_id = h.step_id 
	AND h.step_id <> 0
WHERE j.enabled = 1   --Only Enabled Jobs
and j.name = 'DTA Purge and Archive (BizTalkDTADb)' -- You need to comment if you want to look for all the jobs.
/*
and msdb.dbo.agent_datetime(run_date, run_time) 
BETWEEN '21/07/2017' and '21/07/2017'  --Uncomment for date range queries
*/
order by JobName, RunDateTime desc
GO

 

9,229 thoughts on “SQL Server Agent Job History”

  1. 두 번째, 머리카락이 많이 빠진다. 정상적인
    경우 하루 100개정도의 머리카락이 빠진다. 이는
    심한 스트레스나 여러 요인들에 의한 일시적인 증상으로 약간의 차이가
    날 수도 있지만 갑자기 이보다 더 심하게 머리카락이 빠진다면 탈모일 가능성이 있다.
    세 번째, 머리카락이 가늘어지고 퍼머가 예전처럼 잘 되지 않고 정수리 부분이 훤해지고 핀이 헐거워졌다.

    자신도.

  6. Erkek arkadaşıma oral seks yaptım. Acil Porno 2 ay ago.

    1.38K Views 0 Comments 0 Likes  Merhabalar en çok takip edilen ve
    her zaman bu tür hikayeleri takip eden siz değerli
    seks hikayesi ailesine güzel bir zevkle sizlerde hikayelerin sonuna kadar takip edeceğiniz uzun konulu ve siz
    hikaye sevenlerin.

  20. When I read an article on this topic, baccarat online the first thought was profound and difficult, and I wondered if others could understand.. My site has a discussion board for articles and photos similar to this topic. Could you please visit me when you have time to discuss this topic?

  21. Learn about the side effects, dosages, and interactions. Learn about the side effects, dosages, and interactions.
    https://stromectolst.com/# stromectol coronavirus
    What side effects can this medication cause? Top 100 Searched Drugs.

  49. Some trends of drugs. п»їMedicament prescribing information.
    https://mobic.store/# can you get generic mobic without insurance
    п»їMedicament prescribing information. Learn about the side effects, dosages, and interactions.

  56. Comprehensive side effect and adverse reaction information. Learn about the side effects, dosages, and interactions. https://amoxicillins.online/ amoxicillin without a doctors prescription
    Everything what you want to know about pills. Definitive journal of drugs and therapeutics.

  74. Prescription Drug Information, Interactions & Side. Learn about the side effects, dosages, and interactions.
    https://azithromycins.com/ zithromax online
    drug information and news for professionals and consumers. Medscape Drugs & Diseases.

  90. I’ve been searching for hours on this topic and finally found your post. majorsite, I have read your post and I am very impressed. We prefer your opinion and will visit this site frequently to refer to your opinion. When would you like to visit my site? ?

  131. Testosterone levels do fluctuate over time, but, unless you are right on the edge of what is considered low testosterone, it is unlikely that the natural fluctuation of your hormone levels will lead to the manifestation of the symptoms of low testosterone zithromax bronchitis To test the hypothesis that the therapy could decrease adhesions and therefore improve reproductive organ function, Pilot Study 2 required bilateral tubal occlusion, diagnosed by pre and posttreatment hysterosalpingogram HSG, laparoscopy and or laparotomy

  161. I seriously love your site.. Excellent colors & theme.
    Did you build this web site yourself? Please reply back as I’m
    hoping to create my own site and would like to learn where you got this from or just what the
    theme is called. Kudos!

  212. I’m amazed, I must say. Rarely do I encounter a
    blog that’s both educative and interesting, and let me tell
    you, you have hit the nail on the head. The problem is something which not enough people
    are speaking intelligently about. I am very happy that I stumbled across this during
    my search for something concerning this.

  225. Thank you for any other informative website. The place
    else may I get that type of information written in such a perfect method?
    I’ve a project that I am just now running on, and I’ve been at the look out for such info.

  230. You can certainly see your expertise in the work you write.

    The arena hopes for more passionate writers such as you who are not afraid to
    mention how they believe. All the time go after your
    heart.

  233. Excellent post. I was checking constantly this blog and I’m impressed!
    Very useful info particularly the last part 🙂 I care for such information much.
    I was looking for this certain information for a long time.
    Thank you and best of luck.

  241. Untuk android device, anda boleh file app android dikenali sebagai fail pakej aplikasi android (android application package file – APK file). Fail APK ini adalah greatwall99 apk download for android, khas dicipta oleh pengeluar game GW99 iaitu Citic Application Service Provider untuk membolehkan anda main game ini tanpa melalui Play Store. The hope of a gambler is entirely dependent on the wins. And when you are seeking to make money from the casino, then playing Greatwall99 malaysia is all you need to get started. Is your computer giving you a lot of frustrations and you are losing money frequently? Don’t ignore the potential of your mobile phone or tablet. Download the game and begin your venture. Do not surrender to frustrations at the PlayStation, stay in the house, begin playing, place the bet and become even richer today!!
    http://pokercardsets18.cavandoragh.org/pokerstars-free-tournaments
    Mungkin tidak mudah untuk mendapatkan freespin dengan total 16x, tapi jika kalian mendapatkannya kemenangan kalian sudah pasti didepan mata, tapi tidak terlalu sulit untuk mendapatkan scatter freespin di game ini. Ini menjadi rekomendasi game terbaik buat kalian yang suka game bertema viking. cukup mudah untuk dimainkan namun tentunya harus anda ketahui bahwa untuk mendapatkan suatu jenis kemenangan anda juga mempelajari berbagai macam permasalahan pada jenis permainan tersebut sehingga anda dapat menawarkan kembali peluang penghasilan yang lebih mudah didapatkan. . Maka dari itu pahamilah berbagai tips bermain situs slot terpercaya dan Gacor agar menang lebih mudah karena anda bisa menerapkan tips permainan yang sudah dipahami. Sebelum Anda mulai memainkan mesin slot jenis apa pun, disarankan untuk memahami beberapa solusi bagus untuk diterapkan saat bermain lebih jauh.

  244. I’ve been searching for hours on this topic and finally found your post. baccaratsite, I have read your post and I am very impressed. We prefer your opinion and will visit this site frequently to refer to your opinion. When would you like to visit my site?

  247. Fantastic beat ! I wish to apprentice at the same time as you amend your web site, how can i subscribe for a weblog site?
    The account aided me a acceptable deal. I have been a little bit familiar
    of this your broadcast offered bright clear idea