Most Recent Job Status of All Enabled Jobs

12 Comments on “Most Recent Job Status of All Enabled Jobs”

1. #1 Jen McCown
   on Nov 17th, 2009 at 12:01 pm

   I’ve got $20 on this being extraordinarily easy in PowerShell, but I’m just not there yet…

2. #2 Colin Stasiuk
   on Nov 17th, 2009 at 12:11 pm

   Yeah I’m definitely with you on the PowerShell comment… I think this would be dead simple… now if only I was a PowerShell guru hehehe

   I’m definitely going to work on this in PowerShell and if I get anywhere other then nowhere I’ll post what I get

3. #3 @SQLCrotch
   on Nov 17th, 2009 at 12:45 pm

   I may have something for ya buddy, I’ll send it your way if it fits the bill

4. #4 SQLRockstar
   on Nov 17th, 2009 at 12:53 pm

   did you try msdb..sp_help_jobactivity?

5. #5 Colin Stasiuk
   on Nov 17th, 2009 at 1:04 pm

   I can’t use that cause it’s a SQL 2005+ stored procedure and I still have SQL 2000 in my environment.

   sp_help_jobactivity does look cleaner then sp_help_job and I don’t think I’d get the same:

   Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
   An INSERT EXEC statement cannot be nested.

   error from the looks of the source code behind sp_help_jobactivity… but the whole SQL 2000 thing kills this idea

   Thanks though!

6. #6 @SQLCrotch
   on Nov 17th, 2009 at 3:32 pm

   Hey buddy, give this a shot! Not sure about the 2000 requirement, not sure if SMO was there yet.

   Copy/Paste to a .ps1
   #########################################################################################
   # Job_Failures Script – Used to report status of jobs
   #########################################################################################
   # – Variables
   $instance = “sqldev”
   ##** Uncomment to Loop through text file **
   ## Parse the Servers.txt file (SQL1, SQL2, SQL3) One per line
   ## foreach ($instance in get-content “\\slc-fs01\users\jcrosby\Servers.txt”)
   ## {
   #########################################################################################
   #This script gets SQL Server database information using PowerShell
   [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null

   # Create an SMO connection to the instance
   $s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance
   $s.JobServer.Jobs | Where-Object{$_.IsEnabled -eq $true} | SELECT NAME, LASTRUNOUTCOME, LASTRUNDATE

   ## }

7. #7 @SQLCrotch
   on Nov 17th, 2009 at 3:51 pm

   One thing too, to filter out to see only the faleid, replace this line (16)
   $s.JobServer.Jobs | Where-Object{$_.IsEnabled -eq $true -and $_.LastRunOutCome -ne “Succeeded”} | SELECT NAME, LASTRUNOUTCOME, LASTRUNDATE

   To get a an idea of what objects are available, try puting a SELECT * where SELECT NAME, LAST… etc that’s where those objects are coming from

8. #8 Colin Stasiuk
   on Nov 19th, 2009 at 7:45 am

   SQL Crotch this is fantastical… I’m going to tweak it to link to a SQL 2008 Central Management server and get it’s server list from there… but all in all this is looking like a SQL Bacon Nugget of Excellence award in the making

   Nice work sir… very nice work!

9. #9 Colin Stasiuk
   on Nov 19th, 2009 at 8:07 am

   #########################################################################################
   # Job_Failures Script – Used to report status of jobs
   #########################################################################################
   #Declare the Central Management Server
   $CMSGroup = ‘SQLSERVER:\SQLRegistration\Central Management Server Group\MyCMSSQLServer\MyCMSGroup\’

   #Read the servers list into a variable
   $InstanceListFromCMS = dir $CMSGroup -recurse | where-object { $_.Mode.Equals(“-”) } | select-object Name -Unique

   foreach ($InstanceName in $InstanceListFromCMS)
   {
   $InstanceNameJustName = (Encode-SqlName $InstanceName.Name)
   [System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | out-null
   $s = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) $InstanceNameJustName
   $s.JobServer.Jobs | Where-Object{$_.IsEnabled -eq $true -and $_.LastRunOutCome -eq “Failed”} | SELECT PARENT, NAME, LASTRUNOUTCOME, LASTRUNDATE
   }

10. #10 @SQLCrotch
    on Nov 19th, 2009 at 8:10 am

    Sweetness man! I am happy that worked out. Next blog you’ll have to show us what you are doing with it! Killer bro!

11. #11 Colin Stasiuk
    on Nov 19th, 2009 at 8:12 am

    Not doing much just being anal and not trusting my job notification failures… so this is my adhoc sniff test

    I even have a policy setup to check that all my prod jobs have notification on failure configured… but I have trust issues LOL

12. #12 @littlefuzz
    on Nov 19th, 2009 at 11:33 am

    Good SQL script Colin… It works great for me on 2000/2005/2008. And it’s really slick when ran as multi-server CMS query.

    It’s very useful for our environment and I wonder why I haven’t seen anything like this before!

    +1 bacon nugget

Leave a Comment