SQL Server 2016 PowerShell cmdlets for SQL Agent

By: Rajendra Gupta   |   Updated: 2016-12-22   |   Comments   |   Related: > PowerShell

Problem

In my previous tip, we explored the new PowerShell module SQLSERVER along with new cmdlets to get error log information. In this tip we will see what's new in PowerShell for SQL Server Agent Jobs and explore some of the new cmdlets related with SQL Server Agent.

Solution

As we have seen in previous tip, Microsoft has created a new SQL PowerShell module along with Management Studio in the July release as well as introduced a new wrapper EXE that SSMS uses to instantiate the SQL PowerShell environment. The new version of SQL Server PowerShell included with SSMS does not update the version of PowerShell used by SQL Server. This means that scripts executed by SQL Server Agent will not be able to use these new cmdlets, so SQL Server Agent Jobs will still use the PowerShell module SQLPS.

There are many new cmdlets introduced to retrieve SQL Server Agent Job information. To find the new PowerShell commands for SQL Agent run the below command.

Get-command *sqlagent*

As we can see six PowerShell cmdlets exist for SQL Server Agent Jobs.

• Get-SqlAgent
• Get-SqlAgentJob
• Get-SqlAgentJobHistory
• Get-SqlAgentJobSchedule
• Get-SqlAgentJobStep
• Get-SqlAgentSchedule

Now lets explore each of the cmdlets with examples.

Get-SqlAgent

The Get-SqlAgent PowerShell cmdlet returns a SQL Agent object that is present in the target instance of the SQL Server. We can get more information about this cmdlet using the Get-help command.

Get-Help Get-SqlAgent

If we run the above command with parameter -Full, we get detailed information like description, syntax along with examples.

Get-Help Get-SqlAgent -Full

Below is the example portion of the output, you can get complete output by running the script in your environment.

Now as seen in the example above, let's run the command to get the information in my environment.

Get-SqlAgent -ServerInstance localhost\sql2016

We can see the below this provides SQL Agent information such as agent service account, error log file location, timeout settings, database mail profile, and more.

Get-SqlAgentJob

The Get-SqlAgentJob PowerShell cmdlet returns a SQL Agent Job object for each job that is present in the target instance of SQL Agent. The below command gives information about the command.

Get-Help Get-SqlAgentJob

If we run the above command with parameter - Full, we get detailed information like description, syntax, and examples.

Get-Help Get-SqlAgentJob -Full

Below are examples for Get-SqlAgentJob.

Now as seen in the example above, let's run the command to get the information.

Get-SqlAgentJob -ServerInstance localhost\sql2016

To set the text properly in the window we need to use ft -AutoSize.

 Get-SQlAgentJob -ServerInstance LocalHost\SQL2016 |ft -AutoSize

To get the job information of a particular job use the following with the correct job name.

Get-SQlAgentJob -ServerInstance LocalHost\SQL2016 -Name 'Full Database backup.Subplan_1'|ft -AutoSize

To filter the job further we can filter the results based on its properties. To get information about the member properties we can use below query.

 Get-SqlAgentJob -ServerInstance localhost\sql2016 |Get-Member -Type Properties

So if we want to filter results where last status is failed, run the below query.

(Get-SqlAgentJob -ServerInstance localhost\sql2016 ).where{$_.LastRunOutcome -eq 'Failed'}

If we want a count of failed jobs we can run this code.

(Get-SqlAgentJob -ServerInstance localhost\sql2016 ).where{$_.LastRunOutcome -eq 'Failed'}.Count

Get-SqlAgentJobHistory

The Get-SqlAgentJobHistory PowerShell cmdlet returns the Job History present in the target instance of SQL Server Agent. With the Get-help command we can get the cmdlet properties.

Below are examples for the Get-SQLAgentJobHistory.

If we run this in our environment that has lots of jobs, the result will be very large because of the history.

To get job history for a particular job run this with the correct job name.

Get-SqlAgentJob -ServerInstance localhost\sql2016 -Jobname 'Full Database backup.Subplan_1'

We can use the -since parameter to filter results for specific durations. Accepted values for the since parameter are Midnight, Yesterday, LastWeek and LastMonth.  So if we want to get the job history for a particular job in the last 24 hours we can use the command below.

Get-SqlAgentJobHistory -ServerInstance localhost\sql2016 -Jobname 'Full Database backup.Subplan_1' -since yesterday 

If we want to get only specific fields in the output, we can filter it like below.

Get-SqlAgentJobHistory -ServerInstance localhost\sql2016 -Jobname 'Full Database backup.Subplan_1' -since yesterday |select StepID,Server,RunDate,JobName,StepName,Message

Get-SqlAgentJobSchedule

The Get-SqlAgentJobSchedule PowerShell cmdlet returns a JobSchedule object for each schedule that is present in the target instance of the SQL Server Agent Job. Below is the syntax and description of the cmdlet.

Below are the examples for the Get-SqlAgentJobSchedule.

So if I run it in my environment I get all the SQL Agent job schedules.

Get-SqlAgent -ServerInstance 'localhost\sql2016' | Get-SqlAgentJob | Get-SqlAgentJobSchedule

Get-SqlAgentJobStep

The Get-SqlAgentJobStep PowerShell cmdlet returns a SQL JobStep object for each step that is present in the target instance of a SQL Server Agent Job. Below is the syntax and description for the Get-SqlAgentJobStep.

Examples for the Get-SqlAgentJobStep are:

The below query gives all the job step details of the jobs.

Get-SqlAgent -ServerInstance 'localhost\sql2016' | Get-SqlAgentJob | Get-SqlAgentJobSchedule

If we want to filter the output using step, we can do it using parameter -name as follows.

Get-SqlAgent -ServerInstance 'localhost\sql2016' | Get-SqlAgentJob | Get-SqlAgentJobSchedule -name 'step1'

Get-SqlAgentSchedule

The Get-SqlAgentSchedule PowerShell cmdlet returns a SQL Job Schedule object for each schedule that is present in the target instance of SQL Server Agent. The syntax, description and examples for Get-SqlAgentSchedule are:

The below query returns all schedules on my server.

Get-SqlAgentSchedule -ServerInstance 'localhost\sql2016'

We can see the same schedule using job schedules in SSMS.

If we want to filter the results for the schedule being used by any job we can do the following.

Get-SqlAgentSchedule -ServerInstance 'localhost\sql2016' | ? { $_.JobCount -eq 1 }

These cmdlets make it easier for people to work with SQL Agent Jobs via PowerShell so explore these in your environment.

Next Steps

• Read more about SQL PowerShell: July 2016 update
• Download and explore SQL Server 2016
• Check out SQL Server 2016 tips
• Read more about SQL Server Management Studio with Windows PowerShell Cmdlets
• Read more about SQL Server PowerShell tips

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips