How to Decipher sysschedules

  • minimoajj

    SSC Enthusiast

    Points: 162

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

  • Jeff_C

    Old Hand

    Points: 319

    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: 2684

    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

    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

    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

    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

    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/

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

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