Rotating Header Image

Most Recent Job Status of All Enabled Jobs

 Seems easy enough… and maybe it is easy and someone is going to comment with something fantastical *hint hint hint* but until that happens… I would like… if I may… to take you on a strange journey 🙂

Use Case: I want to be able (using CMS- Central Management Server)  connect to all my SQL Servers (2000, 2005, and 2008) and get the most recent job execution status for all enabled SQL Server Agent Jobs.

At first I thought I was going to be smart (keep your jokes to yourself and try to concentrate 🙂 )…. so like I was saying… at first I thought I was going to be smart and create a temp table to store the value of sp_help_job and then work with the data from there.

Apparently sp_help_job uses INSERT EXEC itself so when I tried to run my fancy code I got this:

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67

An INSERT EXEC statement cannot be nested.

Fail 🙁

So yeah… that pretty well stopped me in my tracks with that idea.   There are a couple tricks to get around that one but I either couldn’t use them cause I’m querying against SQL 2000 still or I wasn’t comfortable with the “workaround” as it felt a bit hack-fu.

So this is what I came up with…

SELECT	A.Name as 'JobName', Sub5.MaxRunDate, Sub5.MaxRunTime, Sub5.MostRecentJobStatus
FROM	msdb.dbo.sysJobs A LEFT OUTER JOIN
		(SELECT	A.Job_ID, Sub4.MaxRunDate, Sub4.MaxRunTime,
				CASE	A.run_status
						WHEN 0 THEN 'Failed'
						WHEN 1 THEN 'Successful'
						WHEN 2 THEN 'Retry'
						WHEN 3 THEN 'Cancelled'
						WHEN 4 THEN 'In Progress'
				END as 'MostRecentJobStatus'
		FROM	msdb.dbo.sysJobHistory A INNER JOIN
				(	SELECT	A.Job_ID, Sub3.MaxRunDate, Sub3.MaxRunTime, Sub3.MaxStepID, MAX(A.Instance_ID) as 'MaxInstanceID'
					FROM msdb.dbo.sysJobHistory A INNER JOIN
						(	SELECT	A.Job_ID, Sub2.MaxRunDate, Sub2.MaxRunTime, MAX(A.Step_ID) as 'MaxStepID'
							FROM msdb.dbo.sysJobHistory A INNER JOIN
								(	SELECT	A.Job_ID, Sub1.MaxRunDate, MAX(A.run_time) as 'MaxRunTime'
									FROM	msdb.dbo.sysJobHistory A INNER JOIN
									(	SELECT	A.Job_ID, MAX(A.run_date) as 'MaxRunDate'
										FROM	msdb.dbo.sysJobHistory A
										GROUP BY A.Job_ID) Sub1 ON
											A.Job_ID = Sub1.Job_ID AND
											A.run_date = Sub1.MaxRunDate
									GROUP BY A.Job_ID, Sub1.MaxRunDate) Sub2 ON
										A.Job_ID = Sub2.Job_ID AND
										A.run_date = Sub2.MaxRunDate AND
										A.run_time = Sub2.MaxRunTime
							GROUP BY A.Job_ID, Sub2.MaxRunDate, Sub2.MaxRunTime) Sub3 ON
									A.Job_ID = Sub3.Job_ID AND
									A.run_date = Sub3.MaxRunDate AND
									A.run_time = Sub3.MaxRunTime AND
									A.Step_ID = Sub3.MaxStepID
						GROUP BY A.Job_ID, Sub3.MaxRunDate, Sub3.MaxRunTime, Sub3.MaxStepID) Sub4 ON
							A.Job_ID = Sub4.Job_ID AND
							A.run_date = Sub4.MaxRunDate AND
							A.run_time = Sub4.MaxRunTime AND
							A.Step_ID = Sub4.MaxStepID AND
							A.Instance_ID = Sub4.MaxInstanceID) Sub5 ON
		A.Job_ID = Sub5.Job_ID
WHERE	A.[Enabled] = 1
ORDER BY A.Name

Now I’m the first to admit that this looks… well horrible…but it seems to work and get me the information I want.

Add in a simple Sub5.MostRecentJobStatus = ‘Failed’ and now you’re querying your whole environment for any job that has failed.

I do have notifications setup to email me whenever a job fails, but being a bit on the anal retentive side I like to have a script like this ready to go just in case I ever want a quick ad-hoc view of things

Like I said at the start of this post… I’m hoping that someone has something much easier to look at and use… so if you got it… flaunt it and comment below!!

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

13 Comments

  1. Jen McCown says:

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

  2. 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. @SQLCrotch says:

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

  4. SQLRockstar says:

    did you try msdb..sp_help_jobactivity?

  5. 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. @SQLCrotch says:

    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. @SQLCrotch says:

    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. 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. #########################################################################################
    # 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. @SQLCrotch says:

    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. 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. @littlefuzz says:

    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

  13. After the already announced Galaxy A series, and the probably soon to be unveiled Galaxy E series, it looks like Samsung will launch the Galaxy J line. This is all part of the company’s new plan to simplify its many, many smartphone offerings in the future, at least when it comes to naming them.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.