A PowerShell Tool to Get the Status of SQL Agent Jobs

Total: 4 Average: 4.8

From a certain point in my career, I started to play around with PowerShell. But I never imagined that it would become such an essential thing in my personal toolset. That’s why I’m here at CodingSight to share my very first PowerShell tool, of many, I truly hope, that any SQL Server DBA can use to enhance the provided service value.

CodingSight - A PowerShell Tool to Get the Status of SQL Agent Jobs

The aim is rather simple – quickly check the current status of SQL Server Agent jobs, either inventory or just the last execution status. You might argue and say, this seems kind of lame because it is something that I can easily do through SQL Server Management Studio. I will agree with you 100%. However, when you are a DBA with dozens/hundreds of instances at your care, things start to get complicated. Especially if you or your company don’t have the budget to spend on fancy third-party monitoring solutions bringing that desired peace of mind.

Initial considerations

Since the focus of this tool revolves around the SQL Agent Jobs, it will not work with variants like Azure SQL Database or SQL Server Express Edition. The tool will require a way to connect to the specified SQL Server instance and retrieve the necessary information. However, it will not succeed if you use an account not having enough rights to access at least the jobs tables within msdb.

You’ll probably be fine if you create a sysadmin account for this specific purpose. However, I encourage you to use the least-privileged principle and grant only the very minimum permissions for the intended account. If that’s the case, you will only need SELECT rights on the following tables:

  • msdb.dbo.sysjobs
  • msdb.dbo.sysjobschedules
  • msdb.dbo.sysschedules

I will be using a test SQL Server instance with a few jobs to demonstrate how to execute the script and which output you should expect from it.

First, the script will ask you for the login type that you want to use to connect to your SQL Server instance. The options are:

  • Trusted – it uses the Windows account you are currently applying to execute the script within the local server.
  • Windows Login – it will prompt you to enter a username and password.
  • SQL Login – it will prompt you to enter a username and password.

How to Use the Script

This article contains the full script of the solution. You only need to place it in some directory – but make sure that the server running that script can reach the target SQL Server Instance(s) through the network.

The script receives 3 parameters:

  • Instance – the name of the target instance.
  • Type of modality – the execution or inventory (execution by default if nothing entered)
    • Execution – lists the last execution status of the jobs within the instance.
    • Inventory – list the inventory of jobs within the instance.
  • Jobs Status – depending on the type of modality selected, it can be one of the following:
    • Execution: Enabled, Disabled, Unscheduled, All.
    • Inventory: Enabled, Disabled, All.

Here’s a matrix of the possible combinations of parameters and the expected outputs:

InstanceType of modalityJobs StatusOutput
XExecutionEnabledLists the last execution statuses of all jobs currently enabled within the instance X.
XExecutionDisabledLists the last execution statuses of all jobs currently disabled within the instance X.
XExecutionUnscheduledLists the last execution statuses of all jobs that currently don’t have a schedule within the instance X.
XExecutionAllLists the last execution statuses of all jobs within the instance X.
XInventoryEnabledLists the inventory of jobs currently enabled within the instance X.
XInventoryDisabledLists the inventory of jobs currently disabled within the instance X.
XinventoryAllLists the inventory of all jobs within the instance X.

Execution Tests                

I am going to demonstrate each way to execute the script along with some screenshots of the output. It should help you to get a pretty good idea of what to expect.

  • The name of my test instance is DESKTOP-QUDLLRR\SQL2.
  • I have placed the script in the directory C:\temp

If you enter a wrong value for the SQL Server instance or specify wrong credentials, the script will notify you about that mistake.

Script notifies about the mistake with wrong value for the SQL Server instance
Login failed for user fake_login

Type of modality = Execution
Jobs Status = Enabled

PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 execution enabled
Execution test with disabled job status

Type of modality = Execution
Jobs Status = Disabled

PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 execution disabled
Execution test with unscheduled job status

Type of modality = Execution
Jobs Status         = Unscheduled

PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 execution unscheduled
Execution test with all job status

Type of modality = Execution
Jobs Status = All

PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 execution all
Execution test with enabled job status

Type of modality = Inventory
Jobs Status = Enabled

PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 inventory enabled
Execution test with disabled job status

Type of modality = Inventory
Jobs Status = Disabled

PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 inventory disabled
Execution test with all job status

Type of modality = Inventory
Jobs Status = All

PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 inventory all
Execution test

The Complete Code of the PowerShell Script

Note the default values in the beginning. The script assumes them if no specific value is passed for each parameter.

param(
    $instance = "localhost",
    $type     = "execution",
    $status   = "all"
)

#Function to execute queries (depending on if the user will be using specific credentials or not)
function Execute-Query([string]$query,[string]$database,[string]$instance,[int]$trusted,[string]$username,[string]$password){
    if($trusted -eq 1){
        try{ 
            Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -ErrorAction Stop -ConnectionTimeout 5 -QueryTimeout 0      
        }
        catch{
            Write-Host -BackgroundColor Red -ForegroundColor White $_
            exit
        }
    }
    else{
        try{
            Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -Username $username -Password $password -ErrorAction Stop -ConnectionTimeout 5 -QueryTimeout 0
        }
         catch{
            Write-Host -BackgroundColor Red -ForegroundColor White $_
            exit
        }
    }
}

$loginChoices = [System.Management.Automation.Host.ChoiceDescription[]] @("&Trusted", "&Windows Login", "&SQL Login")
$loginChoice = $host.UI.PromptForChoice('', 'Choose login type for instance', $loginChoices, 0)
switch($loginChoice)
{
    1 { 
        $login          = Read-Host -Prompt "Enter Windows Login"
        $securePassword = Read-Host -Prompt "Enter Password" -AsSecureString
        $password       = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($securePassword))
      }
    2 { 
        $login          = Read-Host -Prompt "Enter SQL Login"
        $securePassword = Read-Host -Prompt "Enter Password" -AsSecureString
        $password       = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($securePassword))
      }
}

#Attempt to connect to the SQL Server instance using the information provided by the user
try{
    switch($loginChoice){
        0       {$connection = Execute-Query "SELECT 1" "master" $instance 1 "" ""}
        default {$connection = Execute-Query "SELECT 1" "master" $instance 0 $login $password}   
    }     
}
catch{
    Write-Host -BackgroundColor Red -ForegroundColor White $_
    exit
}

#If the connection succeeds, then proceed with the retrieval of the configuration for the instance
Write-Host "   _____  ____  _             _       _         "
Write-Host "  / ____|/ __ \| |           | |     | |        "
Write-Host " | (___ | |  | | |           | | ___ | |__  ___ "
Write-Host "  \___ \| |  | | |       _   | |/ _ \| '_ \/ __|"
Write-Host "  ____) | |__| | |____  | |__| | (_) | |_) \__ \"
Write-Host " |_____/ \___\_\______|  \____/ \___/|_.__/|___/"   

if($type -eq 'inventory'){
    switch($status){
        "enabled"     { $enabled = " AND sysjobs.enabled = 1" }
        "disabled"    { $enabled = " AND sysjobs.enabled = 0" }
        "all"         { $enabled = "" }
    }
}else{
    switch($status){
        "enabled"     { $enabled = " WHERE sj.enabled = 1" }
        "disabled"    { $enabled = " WHERE sj.enabled = 0" }
        "unscheduled" { $enabled = " WHERE sjsch.next_run_date IS NULL" }
        "all"         { $enabled = "" }
    }
}
                                                                                                                                                                                                                                                                                                                                                                                                 
switch($type){
    "inventory"{
                    $jobsQuery = "
                    SELECT
	                sysjobs.name AS 'Job Name',
	                sysjobs.enabled AS 'Enabled',
	                SUSER_SNAME(sysjobs.owner_sid) AS 'Owner',
	                sysjobs.date_created AS 'Date Created',
	                sysjobs.date_modified AS 'Date Modified',
	                CASE
		                WHEN freq_type = 4 THEN 'Daily'
	                END AS 'Frequency',
	                'Every ' + CAST (freq_interval AS VARCHAR(3)) + ' day(s)' AS 'days',
	                CASE
		                WHEN freq_subday_type = 2 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
		                + ' seconds ' + 'starting at '
		                + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
		                WHEN freq_subday_type = 4 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
		                + ' minutes ' + 'starting at '
		                + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
		                WHEN freq_subday_type = 8 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
		                + ' hours '   + 'starting at '
		                + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
		                ELSE 'Starting at ' 
		                + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
	                END AS 'Execution Time'
                    FROM msdb.dbo.sysjobs
                    JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
                    JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
                    WHERE freq_type = 4"+$enabled+"

                    UNION

                    -- jobs with a weekly schedule
                    SELECT
	                    sysjobs.name AS 'Job Name',
	                    sysjobs.enabled AS 'Enabled',
	                    SUSER_SNAME(sysjobs.owner_sid) AS 'Owner',
	                    sysjobs.date_created AS 'Date Created',
	                    sysjobs.date_modified AS 'Date Modified',
	                    CASE	
		                    WHEN freq_type = 8 THEN 'Weekly'
	                    END AS 'frequency',
	                    CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
	                    +CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
	                    +CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
	                    +CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
	                    +CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
	                    +CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
	                    +CASE WHEN freq_interval&1 = 1 THEN 'Sunday' ELSE '' END
	                    AS 'Days',
	                    CASE
		                    WHEN freq_subday_type = 2 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
		                    + ' seconds ' + 'starting at '
		                    + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') 
		                    WHEN freq_subday_type = 4 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
		                    + ' minutes ' + 'starting at '
		                    + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
		                    WHEN freq_subday_type = 8 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
		                    + ' hours '   + 'starting at '
		                    + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
		                    ELSE 'Starting at ' 
		                    + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
	                    END AS 'Execution Time'
	                FROM msdb.dbo.sysjobs
                    JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
                    JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
                    WHERE freq_type = 8"+$enabled+"

                    UNION

                    -- jobs with a monthly schedule
                    SELECT
	                        sysjobs.name AS 'Job Name',
	                        sysjobs.enabled AS 'Enabled',
	                        SUSER_SNAME(sysjobs.owner_sid) AS 'Owner',
	                        sysjobs.date_created AS 'Date Created',
	                        sysjobs.date_modified AS 'Date Modified',
	                    CASE	
		                    WHEN freq_type = 16 THEN 'Monthly'
	                    END AS 'frequency',
	                    CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
	                    +CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
	                    +CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
	                    +CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
	                    +CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
	                    +CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
	                    +CASE WHEN freq_interval&1 = 1 THEN 'Sunday' ELSE '' END
	                    AS 'Days',
	                    CASE
		                    WHEN freq_subday_type = 2 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
		                    + ' seconds ' + 'starting at '
		                    + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') 
		                    WHEN freq_subday_type = 4 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
		                    + ' minutes ' + 'starting at '
		                    + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
		                    WHEN freq_subday_type = 8 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
		                    + ' hours '   + 'starting at '
		                    + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
		                    ELSE 'Starting at ' 
		                    + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
	                    END AS 'Execution Time'
                    FROM msdb.dbo.sysjobs
                    JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
                    JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
                    WHERE freq_type = 16"+$enabled+"

                    UNION

                    -- jobs without a schedule
                    SELECT
	                        sysjobs.name AS 'Job Name',
	                        sysjobs.enabled AS 'Enabled',
	                        SUSER_SNAME(sysjobs.owner_sid) AS 'Owner',
	                        sysjobs.date_created AS 'Date Created',
	                        sysjobs.date_modified AS 'Date Modified',
	                        CASE	
		                        WHEN freq_type = 16 THEN 'Monthly'
	                        END AS 'frequency',
	                        CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
	                        +CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
	                        +CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
	                        +CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
	                        +CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
	                        +CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
	                        +CASE WHEN freq_interval&1 = 1 THEN 'Sunday' ELSE '' END
	                        AS 'Days',
	                        CASE
		                        WHEN freq_subday_type = 2 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
		                        + ' seconds ' + 'starting at '
		                        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') 
		                        WHEN freq_subday_type = 4 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
		                        + ' minutes ' + 'starting at '
		                        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
		                        WHEN freq_subday_type = 8 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
		                        + ' hours '   + 'starting at '
		                        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
		                        ELSE 'Starting at ' 
		                        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
	                        END AS 'Execution Time'
                    FROM msdb.dbo.sysjobs
                    LEFT JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
                    LEFT JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
                    WHERE sysjobschedules.schedule_id IS NULL"+$enabled+"
                    ORDER BY [Job Name]
                    "                        
               }

    "execution"{
                    $jobsQuery = "
                    SELECT
	                    sj.name AS 'Job Name',
	                    CASE 
		                    WHEN sjs.last_run_date > 0 THEN datetimefromparts(sjs.last_run_date/10000, sjs.last_run_date/100%100, sjs.last_run_date%100, sjs.last_run_time/10000, sjs.last_run_time/100%100, sjs.last_run_time%100, 0)
	                    END AS 'Last Run DateTime',
	                    CASE sjs.last_run_outcome
		                    WHEN 0 THEN 'Failed'
		                    WHEN 1 THEN 'Succeeded'
		                    WHEN 2 THEN 'Retry'
		                    WHEN 3 THEN 'Cancel'
		                    WHEN 4 THEN 'In Progress'
		                    WHEN 5 THEN 'Unknown'
	                    ELSE NULL
	                    END AS 'Last Run Status', 
	                    STUFF(STUFF(RIGHT('000000' + CAST(sjs.last_run_duration AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') AS 'LastRunDuration',
	                    sjs.last_outcome_message AS 'Last Run Status Message',
	                    DATETIMEFROMPARTS(sjsch.next_run_date/10000, sjsch.next_run_date/100%100, sjsch.next_run_date%100, sjsch.next_run_time/10000, sjsch.next_run_time/100%100, sjsch.next_run_time%100, 0) AS NextRunDateTime
                    FROM msdb.dbo.sysjobservers sjs
                    LEFT OUTER JOIN msdb.dbo.sysjobs sj ON sj.job_id = sjs.job_id
                    LEFT JOIN msdb.dbo.sysjobschedules sjsch ON sj.job_id = sjsch.job_id
                    "+$enabled+"
                    ORDER BY sj.name
                    "
               }
}

switch($loginChoice){
    0       {$jobs = Execute-Query $jobsQuery "master" $instance 1 "" ""}
    default {$jobs = Execute-Query $jobsQuery "master" $instance 0 $login $password}   
} 

$jobs

Conclusion

I have seen cases where certain SQL Agent jobs should be up and running, but for some reason (probably by someone’s mistake) were disabled. It can cause some troubles for the DBA in charge. However, this tool helps you ensure that it won’t be your case.

Also, I have seen cases where the schedule simply disappeared for some reason. Thus, the SQL Agent jobs would not run automatically anymore. This tool detects such, so you can address them when needed.

The script showcased in this article can serve as a stepping stone to achieving even greater things. For instance, you can modify or enhance it to send you an email when detecting failed jobs.

Besides, you can open multiple PowerShell windows and execute the script against multiple instances under your support. It is one thing that can sometimes a bit difficult to accomplish through SSMS only.

Alejandro Cobar

Alejandro Cobar

Alejandro Cobar is a multi-platform DBA (with a stronger focus in SQL Server), with a passion to automate stuff as much as possible to make things work for the greater good. He began his professional journey as a developer (a bit more than 10 years ago) and has been going back and forth with DBA roles as well, being the latter the one he has mostly developed throughout the years. He also has several Microsoft SQL Server Certifications that have helped him go even further on his skills as a SQL Server Database Administrator.