SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Agent Jobs Statistics


SQL Server Agent Jobs Statistics

Author
Message
aakash9
aakash9
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 372
Comments posted to this topic are about the item SQL Server Agent Jobs Statistics
vesa.juvonen
vesa.juvonen
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 218
Does not even compile
Mark-327565
Mark-327565
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 17
vesa.juvonen (10/11/2013)
Does not even compile


I've copied and pasted the script verbatim and was successful in executing on a SQL 2000 server against the msdb database.
aakash9
aakash9
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 372
There is a small bug in the script Hehe . Updated script will be published soon.
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36598 Visits: 886
Thanks for the script.
francesco.mantovani
francesco.mantovani
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 47
The script is not working on SSMS 2016

Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30368 Visits: 8803
francesco.mantovani - Wednesday, July 26, 2017 4:43 PM
The script is not working on SSMS 2016


It looks like you are in master when you are executing the script. You would need to be in msdb.

Sue



francesco.mantovani
francesco.mantovani
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 47
Sue_H - Wednesday, July 26, 2017 5:40 PM
francesco.mantovani - Wednesday, July 26, 2017 4:43 PM
The script is not working on SSMS 2016


It looks like you are in master when you are executing the script. You would need to be in msdb.

Sue

Yes, you were right, I was in a Master but then run the same script on a database and I received the same error

Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30368 Visits: 8803
francesco.mantovani - Wednesday, July 26, 2017 5:47 PM

Yes, you were right, I was in a Master but then run the same script on a database and I received the same error


I can get the error too if I'm not in msdb. The table is in the msdb databse. That is the database you need to be in when you execute it. At the beginning of the script, before the select, add the following before the select and then try executing it. It's doesn't pull all of the information correctly but it should run:

USE msdb
go


Sue



francesco.mantovani
francesco.mantovani
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 47

Thank you

USE msdbUSE msdb

go


made the trick.

For make it easy to use I added after SELECT

DECLARE @DateFrom char(8) = '20170723' -- Set your start day range

DECLARE @DateTo char(8) = '20170725' -- Set your end day range


By the way I have uncommented, edited and added to the GROUP BY

sJobHis.run_time AS [Running_Time],

sJobHis.run_duration AS [Running_Duration],

But the result is unclear and not easy to understand. I mean the script is awesome but it doesn't show me the big picture:


Wouldn't be easier to show date like 'yyyy/mm/dd' and time like 'hh/mm/ss'?
Can you help me do that?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search