0

I have enabled RSS on few SQL Server recently. I need detailed output of job with it's steps completion duration pre and post enabling RSS. If anyone have the script, please do share. Script for single as well as multiple servers will be helpful.

Basically, I need to check is there any improvement in job duration after enabling RSS.

Any help here is highly appreciated. Thanks!

Iam_NC
  • 45
  • 6
  • SQL Agent has a job log which you can export. What issue do you have in particular with this? There are many online script that extract data from SQL Agent logs. Can you find one and explain why it doesn't fulfill your needs? – Nick.Mc Aug 31 '17 at 06:37
  • Thanks for the reply Nick. I'll explain with an example here. Say if I have a job with 4 steps; need to take the total job duration only when the job execution is completed end to end i.e. job execution moves from step1 then step2… and so on. And I need script for this. Exporting from SQL Agent Logs doesn’t being displayed in proper format. All data in first single column. *Don’t know how to work on it* And as I’ve mentioned, I need to check if there’s any improvement in job duration after enabling RSS. Please Help! – Iam_NC Sep 03 '17 at 11:55
  • Let me guess: you opened the exported log in excel? You need to use text to columns to get that into multiple columns. As far as 'check' any improvement' - I guess as long as you can list all the fully running job and their times, you can work that out yourself by inspection (you could even build a graph in excel) – Nick.Mc Sep 03 '17 at 22:58
  • To my suprise this doesn't seem possible with job queries. As far as I can tell there is no key in `msdb.dbo.sysjobhistory` that ties together a bunch of steps to a single job. So you can get the total runtime of a job easily enough , you just can't work out what steps were involved.Somehow SSMS does it though. I might do some more digging – Nick.Mc Sep 03 '17 at 23:41
  • @Nick.McDermaid I searched a lot and found one query to get job step duration as well. I don’t exactly remember the site, but I will share the query here for saving further time. As the code is bit lengthy, _Stackoverflow_ isn’t allowing to paste the code and getting this message: **too long by 1559 characters**. Don’t exactly know as I am new here. And about exporting to excel and formatting it to desired output, I figured it out. Thanks again Nick!! – Iam_NC Sep 04 '17 at 15:48
  • Link to the Query: [link] (http://www.sqldbpros.com/2013/08/sql-job-steps-and-run_duration-the-query-that-formats-run_duration-correctly-and-doesnt-make-you-want-to-shoot-your-eye-out/) – Iam_NC Sep 05 '17 at 10:38
  • Thanks for returning with the link. What that query doesn't do (and I can't work out how to do because there are no relationships), is group the steps together into a single job instance. In other words it lists a bunch of steps under a job but there is no guarantee that list of steps all ran together in the same job execution. – Nick.Mc Sep 05 '17 at 11:07

0 Answers0