PowerShell Function to Check the Status of a SQL Agent Job using the .NET Framework

My previous blog article demonstrated how to start a SQL agent job using the .NET Framework from PowerShell to eliminate the dependency of needing the SQL Server PowerShell module or snap-in on the machine where the command is being run from.

There’s not much use of blindly starting a SQL agent job without being able to check the status of it so I decided to write another function to accomplish that task. If the machine where the code is being run from has the SQL Server PowerShell module that installs as part of SQL Server Management Studio 2016, then you could simply use existing cmdlets without having to use this custom function.

#Requires -Version 3.0
function Get-MrSqlAgentJobStatus {

    Retrieves the status of one or more SQL Agent Jobs from the specified target instance of SQL Server.

    Get-MrSqlAgentJobStatus is a PowerShell function that is designed to retrieve the status of one or more SQL
    Server Agent jobs from the specified target instance of SQL Server without requiring the SQL Server PowerShell
    module or snap-in to be installed.

.PARAMETER ServerInstance
    The name of an instance of SQL Server where the SQL Agent is running. For default instances, only
    specify the computer name: MyComputer. For named instances, use the format ComputerName\InstanceName.

    Specifies the name of one or more Job objects that this cmdlet gets. The names may or may not be
    case-sensitive, depending on the collation of the SQL Server where the SQL Agent is running.

.PARAMETER Credential
    SQL Authentication userid and password in the form of a credential object.

     Get-MrSqlAgentJobStatus -ServerInstance SQLServer01 -Name syspolicy_purge_history, test

     Get-MrSqlAgentJobStatus -ServerInstance SQLServer01 -Name syspolicy_purge_history -Credential (Get-Credential)

     'syspolicy_purge_history', 'test' | Get-MrSqlAgentJobStatus -ServerInstance SQLServer01



    Author:  Mike F Robbins
    Website: http://mikefrobbins.com
    Twitter: @mikefrobbins

    param (


        [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty

    BEGIN {

        $Params = @{
            ServerInstance = $ServerInstance
            Database = 'msdb'

        if ($PSBoundParameters.Credential) {
            $Params.Credential = $Credential


        foreach ($n in $Name) {

            $Params.Query = "EXEC dbo.sp_help_job @JOB_NAME = '$n', @job_aspect= 'JOB'"
            $Results = Invoke-MrSqlDataReader @Params |
                       Select-Object -Property originating_server, name, last_run_outcome, current_execution_status

                ComputerName = $Results.originating_server
                Name = $Results.name
                Outcome = switch ($Results.current_execution_status) {
                              1 {'Executing';break}
                              2 {'Waiting for thread';break}
                              3 {'Between retries';break}
                              4 {'Idle';break}
                              5 {'Suspended';break}
                              7 {'Performing completion actions';break}
                              default {'An unknown error has occurred'}
                Status = switch ($Results.last_run_outcome) {
                             0 {'Failed';break}
                             1 {'Succeeded';break}
                             3 {'Canceled';break}
                             5 {'Unknown';break}
                             default {'An unknown error has occurred'}




Specify the SQL instance name and job name to return the status. There’s also a credential parameter to specify SQL authentication if needed.

Get-MrSqlAgentJobStatus -ServerInstance SQL011 -Name syspolicy_purge_history


Multiple jobs can also be retrieved via parameter or pipeline input:

Get-MrSqlAgentJobStatus -ServerInstance SQL011 -Name syspolicy_purge_history, test
syspolicy_purge_history', 'test' | Get-MrSqlAgentJobStatus -ServerInstance SQL011


The Get-MrSqlAgentJobStatus function shown in this blog article can be downloaded from my SQL repository on GitHub. This function depends on my Invoke-MrSqlDataReader function which is also part of my MrSQL PowerShell module which can be found in the same repo.