Recently I needed to take a look at all of the SQL Server Agent Jobs and their Jobs Steps for a customer. Specifically, I needed to review all of the Jobs and Job Steps for Ola Hallengren’s Maintenance Solution and look at the Backup, Index Maintenance and Integrity Jobs to ensure they’re configured properly and also account for any customizations and one-offs in the Job definitions. This customer has dozens of SQL Server instances and well, I wasn’t about to click through everything in SSMS…and writing this in TSQL would have been a good candidate for a Ph.D. dissertation. So let’s check out how I solved this problem using dbatools.
Enter dbatools…
In my first attempt at doing this I tried getting all the Jobs using Get-DbaAgentJob
and exporting the Jobs to TSQL using Export-DbaScript
. This did give me the code for all of the Jobs I was interested in. But that left me trying to decipher SQL Agent Job and Schedule syntax and encodings and I got all twisted up in the TSQL-ness of that. I needed this to be more readable.
So I thought…there has to be a better way…there is! So, I wrote the following. This code gets each SQL Agent Job, print the Job’s Name, NextRunDate, if it has a Schedule, Operator information, and then for each JobStep it prints the Step’s Name, Subsystem, and finally the Command. Using this I can quickly get a feel for the configurations across the environment.
Get a listing of all SQL Instances
$Servers = Get-DbaRegisteredServer
Get all of the SQL Agent Jobs across all SQL Instances
$jobs = Get-DbaAgentJob -SqlInstance $Servers.Name
Filter that list down to the SQL Agent Jobs that are in the Database Maintenance category
$MaintenanceJobs = $jobs | Where-Object { $_.Category -eq 'Database Maintenance' }
For each SQL Agent Job, print the Job’s Name, NextRunDate, if it has a Schedule, Operator information, and then for each JobStep print its Name, Agent Subsystem, and finally the Command.
$JobsAndSteps = foreach ($MaintenanceJob in $MaintenanceJobs){
foreach ($JobStep in $MaintenanceJob.JobSteps) {
$obj = [PSCustomObject]@{
SqlInstance = $MaintenanceJob.SqlInstance
Name = $MaintenanceJob.Name
NextRunDate = $MaintenanceJob.NextRunDate
HasSchedule = $MaintenanceJob.HasSchedule
OperatorToEmail = $MaintenanceJob.OperatorToEmail
JobStepName = $JobStep.Name
SubSystem = $JobStep.SubSystem
Command = $JobStep.Command
}
$obj
}
}
Here’s some sample output using Format-Table
. From there I can quickly scan and analyze all the Jobs on all of the Instances in an environment.
$JobsAndSteps | Format-Table
SqlInstance Name NextRunDate HasSchedule OperatorToEmail JobStepName SubSystem Command
----------- ---- ----------- ----------- --------------- ----------- --------- -------
PRODSQL1 DatabaseBackup - USER_DATABASES - FULL 2/3/2021 1:00:00 AM True DbaTeam DatabaseBackup - USER_DATABASES - FULL - Backup CmdExec sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'T:\Backup', @Ba...
PRODSQL1 DatabaseBackup - USER_DATABASES - FULL 2/3/2021 1:00:00 AM True DbaTeam DatabaseBackup - USER_DATABASES - FULL - Sync CmdExec ROBOCOPY SOME STUFF
PRODSQL1 DatabaseBackup - USER_DATABASES - FULL 2/3/2021 1:00:00 AM True DbaTeam DatabaseBackup - USER_DATABASES - FULL - Cleanup PowerShell RUN SOME POWERSHELL TO DO COOL STUFF
PRODSQL2 DatabaseBackup - USER_DATABASES - FULL 2/3/2021 1:00:00 AM True DbaTeam DatabaseBackup - USER_DATABASES - FULL - Backup CmdExec sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'T:\Backup', @Ba...
PRODSQL2 DatabaseBackup - USER_DATABASES - FULL 2/3/2021 1:00:00 AM True DbaTeam DatabaseBackup - USER_DATABASES - FULL - Sync CmdExec ROBOCOPY SOME STUFF
PRODSQL2 DatabaseBackup - USER_DATABASES - FULL 2/3/2021 1:00:00 AM True DbaTeam DatabaseBackup - USER_DATABASES - FULL - Cleanup PowerShell RUN SOME POWERSHELL TO DO COOL STUFF
You can also take that output and convert it to CSV and then Excel for analysis
$JobsAndSteps | ConvertTo-Csv -NoTypeInformation | Out-File JobSteps.csv
Love this!
Is there a way to list Azure SQL Managed Instances using two-factor authentication?
If not, how would I edit the $servers array to remove the managed instances and other servers that can’t be reached?
Regarding MFA, see this link for examples: https://docs.dbatools.io/#Connect-DbaInstance. Regarding removing Managed Instances…try using one of the discovery cmdlets to pull the version or properties and filter on that see
Get-DbaInstanceProperty
orConnect-DbaInstance
$SQLs = dir ‘SQLSERVER:\SQLRegistration\Database Engine Server Group’
Get-SqlAgentJob -ServerInstance $SQLs.Name |
Where-Object { $_.Category -eq ‘Database Maintenance’ } |
Get-SqlAgentJobStep
Yes, that will certainly get the job information and I used that in my initial attempt to get the job info. But I’m interested in a subset of the information, in including the Commands, in a quickly consumable format.