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.
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.
Explanation of PowerShell Script Using
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:
| Instance | Type of modality | Jobs Status | Output |
| X | Execution | Enabled | Lists the last execution statuses of all jobs currently enabled within the instance X. |
| X | Execution | Disabled | Lists the last execution statuses of all jobs currently disabled within the instance X. |
| X | Execution | Unscheduled | Lists the last execution statuses of all jobs that currently don’t have a schedule within the instance X. |
| X | Execution | All | Lists the last execution statuses of all jobs within the instance X. |
| X | Inventory | Enabled | Lists the inventory of jobs currently enabled within the instance X. |
| X | Inventory | Disabled | Lists the inventory of jobs currently disabled within the instance X. |
| X | inventory | All | Lists the inventory of all jobs within the instance X. |
How to Get SQL Server Agent Jobs Status
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.
Type of modality = Execution
Jobs Status = Enabled
PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 execution enabled
Type of modality = Execution
Jobs Status = Disabled
PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 execution disabled
Type of modality = Execution
Jobs Status = Unscheduled
PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 execution unscheduled
Type of modality = Execution
Jobs Status = All
PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 execution all
Type of modality = Inventory
Jobs Status = Enabled
PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 inventory enabled
Type of modality = Inventory
Jobs Status = Disabled
PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 inventory disabled
Type of modality = Inventory
Jobs Status = All
PS C:\temp> .\Get-Instance-Jobs.ps1 DESKTOP-QUDLLRR\SQL2 inventory all
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.
Tags: powershell, sql agent jobs, sql server agent Last modified: March 31, 2023
