Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 PowerShell cmdlets for SQL Agent


By:   |   Last Updated: 2016-12-22   |   Comments   |   Related Tips: More > 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

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

Get-Help Get-SqlAgent PowerShell cmdlet

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.

Get-Help Get-SqlAgent -Full PowerShell cmdlet

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-SqlAgent -ServerInstance localhost\sql2016 PowerShell cmdlet

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

Get-Help Get-SqlAgentJob PowerShell cmdlet

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.

Get-Help Get-SqlAgentJob -Full PowerShell cmdlet

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

Get-SqlAgentJob -ServerInstance localhost\sql2016

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 set the text properly in the window we need to use 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 get the job information of a particular job use the following with the correct job name.

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

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

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'}
So if we want to filter results where last status is failed, run the below query.

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

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

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

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.

The Get-SqlAgentJobHistory PowerShell cmdlet returns the Job History present in the target instance of SQL Agent

Below are examples for the Get-SQLAgentJobHistory.

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.

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'

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 


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-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.

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 are the examples for the Get-SqlAgentJobSchedule.

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-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.

Syntax and description for the Get-SqlAgentJobStep

Examples for the Get-SqlAgentJobStep are:

Examples for the Get-SqlAgentJobStep

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

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

All the SQL Server Agent Job Step details

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-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 Get-SqlAgentSchedule PowerShell cmdlet returns a SQL Job Schedule object
overview

The below query returns all schedules on my server.

Get-SqlAgentSchedule -ServerInstance 'localhost\sql2016'

Get-SqlAgentSchedule -ServerInstance 'localhost\sql2016'

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

Job Schedules in SQL Server Management Studio

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 }

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


Last Updated: 2016-12-22


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools