How to Decipher sysschedules

Question

How to Decipher sysschedules

minimoajj

SSC Enthusiast

Points: 162
More actions
June 21, 2017 at 10:26 pm

#402205

Comments posted to this topic are about the item How to Decipher sysschedules

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff_C Old Hand Points: 344 More actions · Answer 1

Hi Alan,

Nice post. Very informative. Generally I find that the smo objects are more easily interpretable than trying to access the information via TSQL, so I had a quick go at doing something in powershell. It still needs work but the basics are there for people to play with. Just rename the attached file to .ps1.

Regards

Jeff.

Lee Linares SSCrazy Points: 2727 More actions · Answer 2

Thanks Allan for taking the time to share this with us.
I was wondering if it would be easier to use the msdb.dbo.agent_datetime function to replace all that code to make the date and time readable.

This line could be used in your first query to get the last_run dataetime:

last_run=CASE sjs.last_run_date WHEN 0 THEN NULL ELSE msdb.dbo.agent_datetime(sjs.last_run_date,sjs.last_run_time) END ,

Thanks again.

Lee

minimoajj SSC Enthusiast Points: 162 More actions · Answer 3

Thanks Jeff,

I always like to find other ways to do things. One thing I've learned over the years is that the more ways you know how to do something, the easier it is to get around road blocks that always seem to come up. SMOs and powershell in general are some tools that I've not mastered yet. I've used them here and there but it is definitely something that I need to add to my skill set.

Regards,

Alan

minimoajj SSC Enthusiast Points: 162 More actions · Answer 4

Thanks, Lee,

I was looking for a function to do just that but It doesn't seem to be documented in BOL (at least not in 2008 r2). That is definitely a more elegant solution. From a quick internet search, it appears that msdb.dbo.agent_datetime was first included in SQL 2005. Good catch!

Regards,

Alan

cyp901 Mr or Mrs. 500 Points: 539 More actions · Answer 5

The main query here looks a lot like the one that has been around for a while. I'm not entirely sure where it came from originally.
The only difference is that this one appears to have been run through a SQL formatting tool.
Probably just a coincidence.

minimoajj SSC Enthusiast Points: 162 More actions · Answer 6

cyp901 - Thursday, June 22, 2017 7:58 AM
The main query here looks a lot like the one that has been around for a while. I'm not entirely sure where it came from originally.
The only difference is that this one appears to have been run through a SQL formatting tool.
Probably just a coincidence.

Yes, as I mentioned in the article, I began with the logic from this article and expanded on it.
http://www.sqlphilosopher.com/wp/2013/01/sql-agent-job-frequency-intervals-in-plain-english/