New PowerShell Commands from the SQL Server Team!

Have you seen the SQL Server Tools team's post about the PowerShell module for SQL Server? There are TWENTY FIVE new cmdlets! TWENTY FIVE! That's a 53% increase over the previous 46 cmdlets. It looks like the SQL Server community is well on its way to becoming proper citizens in the PowerShell world, and we'll reach Exchange's 700+ in no time ;)

Included in the 25 new cmdlets is a whole lot more support for SQL Server Agent. This batch was chosen because of community upvoting on the Trello boards. We literally asked for this and got it. If you haven't participated in the design of the cmdlets yet, head over now and start upvoting.

SQL Server Agent

It's now so much easier to work with SQL Server Agent jobs. But before we dig in, here are the five new cmdlets dedicated to SQL Server Agent.

One of the best things about these cmdlets is that it makes failed jobs a whole lot easier to find. Note that SQL Server Management Studio 2016 does not have to be installed on the server -- only your workstation. That's because the cmdlet is built on top of SMO which is available in all versions of SQL Server since 2000.

As an aside, you may be wondering what a cmdlet is, and what's the difference between a cmdlet and what you and I write. A cmdlet is a formally written PowerShell command that's written in C#. I've never written one in my life. Instead, I write Functions. They're both essentially the same, but a cmdlet is fancier on the back end.

Let's see some code!

Let's start by specifying our SQL Server name dynamically so that you can easily copy and paste from this blog. In my case, $sqlserver = "sql2012"

1Get-SqlAgent -ServerInstance $sqlserver | Get-SqlAgentJob | Where-Object { $_.IsEnabled -eq $true -and $_.LastRunOutcome -eq 'Failed' }

Now you can pipe the Agent server to the next command, but you can also explicitly specify the SQL Server in the cmdlet itself. You can also specify the -Since the get events that have occurred since a specific date. Valid values incldue Midnight, Yesterday, LastWeek and LastMonth. But you don't have to remember this, you can just tab through the values after typing -Since.

1Get-SqlAgentJobHistory -ServerInstance $sqlserver -Since LastMonth

Want to know all the jobs associated with a specific schedule? While you can do this to get the schedules.

1Get-SqlAgentJob -ServerInstance $sqlserver -Name 'DatabaseBackup - USER_DATABASES - FULL'  | Get-SqlAgentJobSchedule

What if you have duplicate names? Let's pick with Out-GridView and pass that to Get-SqlAgentJob.

1$schedulenames = (Get-SqlAgentSchedule -ServerInstance $sqlserver | Select Id, Name | Out-GridView -Passthru).Name
2Get-SqlAgentJob -ServerInstance $sqlserver  | Where-Object { $_.JobSchedules -in $schedulenames }

And finally, want to find the failed step within a failed job? Here's one way you can do it.

1$failedjobs = Get-SqlAgent -ServerInstance $sqlserver | Get-SqlAgentJob | Where-Object { $_.IsEnabled -eq $true -and $_.LastRunOutcome -eq 'Failed' }
2
3foreach ($job in $failedjobs) {
4    $steps = $job | Get-SqlAgentJobStep
5    $steps.Where({ $_.LastRunOutcome -ne 'Successful' })
6}

In the future, we'll have corresponding Set cmdlets that can address any issues that are found. Until then, we have these super useful cmdlets that make it way easier to work with jobs in PowerShell.

Great work, team! Want to see more? Aaron Nelson wrote and Rob Sewell wrote a lengthy article about new cmdlets. I wrote up a lil something about them, too.