By: Aaron Nelson | Updated: 2017-02-23 | Comments (1) | Related: > SQL Server Agent
Problem
SQL Server Management Studio (SSMS) doesn't give you enough at-a-glance information about your SQL Server Agent Jobs. You're forced into using "Job Activity Monitor" which pops up in a separate window and cannot be docked inside of SSMS like other windows such as Object Explorer Details, [Query] Properties, Registered Servers, etc. Worse yet, if you want to find the status of agent jobs across multiple instances, your most straight-forward option is limited to running T-SQL queries, which you have to start from Registered Servers in order to query across multiple instances.
Solution
SQL PowerShell 2016 starting with the July update of SSMS 2016 now includes 6 cmdlets for working with SQL Agent objects. Those cmdlets are: Get-SqlAgent, Get-SqlAgentJob, Get-SqlAgentJobHistory, Get-SqlAgentJobSchedule, Get-SqlAgentJobStep and Get-SqlAgentSchedule.
To get this full list of SQL Agent commands in PowerShell, just use the Get-Command cmdlet with these parameters:
Get-Command -Module SqlServer -Noun SqlAgent*
Out of those commands I find Get-SqlAgentJob and Get-SqlAgentJobHistory the most useful to me personally, so we'll start there.
Get-SqlAgentJobHistory PowerShell 2016 cmdlet
This is a fun article to write because PowerShell makes achieving the title this simple:
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -Since Midnight -OutcomesType Failed
In the code sample above we called the Get-SqlAgentJobHistory and passed multiple instances to it simply by adding a comma in-between each of them, then we used the -Since parameter and picked the 'Midnight' option from its list of options.
For readability reasons, I highly recommend outputting these results to the Out-GridView cmdlet, like this.
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -Since Midnight -OutcomesType Failed | Out-GridView
Using a Different Start Time
While it's great that we can pull back that kind of information with such a short command, it only helps you if all the jobs you're worried about start after midnight. You could change the option for the -Since parameter to 'Yesterday' which will retrieve all the job history since midnight the previous day. For some folks this might be too much information and you basically want to focus on just the jobs that you have run since you went home yesterday. In that case you could use the -StartRunDate parameter and pass it something like "(get-date).AddHours(-12)" which will search all jobs that have started in the last 12 hours.
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -StartRunDate (get-date).AddHours(-12) -OutcomesType Failed
Alternatively, you could pass in a simple date like below. It's up to you and what you feel the most comfortable with.
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -StartRunDate 2017-01-31 -OutcomesType Failed
Options for Querying Multiple Instances
Another option available to us on this same command is how we input the list of SQL Server instances we want the command to go out and check. The first example was a simple in-line comma separated list and sure that works, but if you have a large number of SQL Servers you aren't going to want to have to retype them every day. PowerShell makes it possible to grab a list of servers from a text file, a table in SQL Server, and several other places; however, my favorite place to grab a list of SQL Servers from is Registered Servers / Central Management Servers. You can easily find either of these options by running this command DIR SQLSERVER:\SQLRegistration.
You could include a call to Registered Servers in-line with the call to the Get-SqlAgentJobHistory cmdlet, but in the example below we will first load all of those Registered SQL Servers into a PowerShell variable called "$ServerList" to streamline things.
$ServerList = DIR -Recurse 'SQLSERVER:\SQLRegistration\Database Engine Server Group\Host'| WHERE {$_.Mode -ne 'd'}
Once we have all those SQL Server instances loaded up into our $ServerList variable we can use that with our -ServerInstance parameter, however, PowerShell works with objects, and objects have properties, and in this case we are going to want the Name property of the $ServerList variable object, like this: $ServerList.Name
Get-SqlAgentJobHistory -ServerInstance $ServerList.Name -StartRunDate (get-date).AddHours(-12) -OutcomesType Failed | Out-GridView
Now we have as many SQL Server instances as we have passed in returning all of their failed SQL Agent Jobs over the last 12 hours with a very short amount of code.
Another option for querying multiple instances is to loop through them. You can do this with a simple foreach loop. This option might come in handy if some of the instances you are querying have slow connections because at least you will be seeing the instances that have responded while it continues to loop through the rest of the instances. Instead of waiting to get the results all at once.
foreach ($ServerList IN dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ ) { Get-SqlAgentJobHistory -ServerInstance $ServerList.Name -StartRunDate (get-date).AddHours(-12) -OutcomesType Failed | Format-Table -AutoSize }
Note: In the code block above I also switched to outputting the results using the Format-Table cmdlet with its -AutoSize parameter. You could also choose not to pipe the output to anything and it would default to coming back in a list, as if you had piped the results to Format-List.
Double Results?
No. Just a quick note about what the output from the Get-SqlAgentJobHistory cmdlet is telling you. It may look like the Get-SqlAgentJobHistory cmdlet is returning duplicate results, but what it is returning is one 'row' telling you about the job itself, and then another 'row' for each step in the job. This is precisely the same behavior you experience today in SSMS when you right-click on a Job and "View History".
Get-SqlAgentJob PowerShell 2016 cmdlet
With the Get-SqlAgentJob cmdlet we are able to do the functional equivalent of going into SSMS and navigating to SQL Agent > Jobs and viewing them in Object Explorer Details, except that we have a ton more information at our finger tips.
Get-SqlAgentJob -ServerInstance localhost
This cmdlet will try to return Name, Owner, Category, Enabled, CurrentRunStatus, DateCreated, LastModified and LastRunDuration however, depending on your screen resolution & sizing it may not be possible for that to all fit on your screen. Again, Out-GridView can help you out here.
Get-SqlAgentJob -ServerInstance localhost | Out-GridView
In addition to the 8 properties mentioned above, there are 33 more properties that you can access (for a total of 41). An easy way to see all of these is to pipe the output to SELECT[-Object] * like this:
Get-SqlAgentJob -ServerInstance localhost | SELECT *
With that expanded list of properties available, we now know that there are different questions or queries we can ask like: looking for all the jobs with an 'IsEnabled' property = 'True' and a LastRunOutcome = 'Failed'.
Get-SqlAgentJob -ServerInstance localhost | WHERE { $_.IsEnabled -eq $true -and $_.LastRunOutcome -eq 'Failed' }
Or maybe we want to see all of the SQL Server Agent Jobs created in the last week.
Get-SqlAgentJob -ServerInstance localhost | WHERE { $_.DateCreated -gt (get-date).AddDays(-7) }
Start-SqlAgentJob PowerShell 2016 cmdlet
Finally, how about re-starting some of those failed SQL Agent Jobs? Here's a scenario for you: Maybe the jobs that failed on you last night were all jobs that rely on some remote server and it was down in the middle of the night. That server is online now and you need to kick off a bunch of jobs all at once. Unfortunately there is not a Start-SqlAgentJob cmdlet [yet], but since PowerShell work with objects we can still easily start SQL Agent Jobs without one.
Before I show you how to start a SQL Agent Job, I want to show you how I found out I can start a SQL Agent Job. Call the Get-SqlAgentJob cmdlet and pipe its output over to the Get-Member cmdlet with the -MemberType parameter set to Methods, this will show you all of the methods available to you.
Get-SqlAgentJob -ServerInstance localhost | Get-Member -MemberType Methods
Note: in the interest of screen real estate I have shortened the list. There are over 40 methods available to you on SQL Agent Jobs.
From here I saw that there was a Start method available on the Jobs, meaning that I can take the output of the Get-SqlAgentJob cmdlet and start any job that comes out. In this case I'm going to add two filters first, so that I can first slim down and then choose which jobs to start. You'll see first two lines are identical to what I used earlier in this article, but then I pipe those results over to the Out-GridView cmdlet and include the -PassThru parameter so that we can hand pick which jobs to Start. Finally, the jobs that were chosen will get piped to the foreach command which will start them one at a time.
If you just want an easy way to start SQL Agent Jobs across multiple machines, regardless of whether they had previously failed or not, just remove the line of code with the 'WHERE' and use the other three lines and you're ready to do that.
Get-SqlAgentJob -ServerInstance localhost, localhost\SQL2016 | WHERE { $_.IsEnabled -eq $true -and $_.LastRunOutcome -eq 'Failed' } | Out-GridView -PassThru | foreach {$_.Start()}
Modify SQL Agent Jobs in Bulk
A lot of properties on SQL Agent Jobs can be modified. To find out which ones, use the Get-Member cmdlet with the -MemberType parameter set to Properties and then look for properties which display "set;" in the "Definition" column.
Get-SqlAgentJob -ServerInstance localhost | Get-Member -MemberType Properties
With this in mind we could do something like change the owner on multiple SQL Agent Jobs by picking the ones we want to modify with a filter or with Out-GridView cmdlet and then sending those Jobs to a foreach loop that sets the OwnerLoginName property to 'sa' and then runs an .Alter() method on the job.
Get-SqlAgentJob -ServerInstance localhost, localhost\SQL2016 | Out-GridView -PassThru | foreach { $_.OwnerLoginName = 'sa' $_.Alter() }
Once you selected the jobs you want to modify and click the "OK" button the command will resume and go modify all the SQL Agent Jobs you just selected. This small amount of code could come in handy if you needed to enable or disable a bunch of jobs at once. You're only limited to what you have permissions to do, and which properties offer you to "set;" them.
Obviously, you could extend the example above using the other techniques we've covered in this article or by using one of those methods available to you.
Next Steps
- For more information on the New SqlServer module see this post on the SQL Server team blog.
- To download the latest version of SSMS 2016.
- Check out my tip on using Invoke-SQLCmd to INSERT data into your SQL Servers from other servers.
About the author
Aaron Nelson is a Senior SQL Server Architect with over 10 years experience in architecture, BI, development and tuning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-02-23
