T-SQL Tuesday #186 - Agent Job History Visualization

T-Sql-Tuesday, Dbatools, Powershell
Page content

I’m hosting T-SQL Tuesday this month and our topic is Managing SQL Agent Jobs. In this post, we’ll be taking a look at a very handy combination of dbatools functions for looking at agent job schedules & performance.

T-SQL Tuesday Logo

Getting Job History

If you don’t have a monitoring suite watching SQL Agent, or you want to get a bigger picture view of when and how your Agent jobs are running, dbatools can help you shortcut getting this information. Get-DbaAgentJobHistory does exactly what its name suggests - it fetches the execution history for one or more Agent jobs on one or more SQL Server instances. Thing is…it can produce a lot of output if you’re not careful. By default, it’ll return the entire job history, with at least two objects per job execution. For example, this is the partial output of Get-DbaAgentJobHistory -SqlInstance localhost\sql19:

ComputerName    : vader
InstanceName    : SQL19
SqlInstance     : vader\SQL19
Job             : CommandLog Cleanup
StepName        : (Job outcome)
RunDate         : 2025-05-12 08:23:04
StartDate       : 2025-05-12 08:23:04.000
EndDate         : 2025-05-12 08:23:04.000
Duration        : 0 ms
Status          : Succeeded
OperatorEmailed :
Message         : The job succeeded.  The Job was invoked by User vader\andyl.  The last step to run was step 1
                  (CommandLog Cleanup).

ComputerName    : vader
InstanceName    : SQL19
SqlInstance     : vader\SQL19
Job             : CommandLog Cleanup
StepName        : CommandLog Cleanup
RunDate         : 2025-05-12 08:23:04
StartDate       : 2025-05-12 08:23:04.000
EndDate         : 2025-05-12 08:23:04.000
Duration        : 0 ms
Status          : Succeeded
OperatorEmailed :
Message         : Executed as user: NT SERVICE\SQLAgent$SQL19. The step succeeded.

That’s just one run of a single-step job on a single instance. Now expand that to a couple dozen jobs, each with multiple steps, on a handful of instances. It’ll get out of control in a hurry. Fortunately, this function has a few filtering options, in particular -ExcludeJobSteps to limit the output to the overall job result, -Job and -ExcludeJob to include or exclude jobs by name, and -StartDate and -EndDate to limit the date range of the history. This tidies things up quite a bit.

1

Get-DbaAgentJobHistory -SqlInstance localhost\sql19 -ExcludeJobSteps -Job "CommandLog Cleanup","DatabaseBackup - SYSTEM_DATABASES - FULL" -StartDate (Get-Date).AddDays(-1)`
ComputerName    : vader
InstanceName    : SQL19
SqlInstance     : vader\SQL19
Job             : CommandLog Cleanup
StepName        : (Job outcome)
RunDate         : 2025-05-12 08:23:04
StartDate       : 2025-05-12 08:23:04.000
EndDate         : 2025-05-12 08:23:04.000
Duration        : 0 ms
Status          : Succeeded
OperatorEmailed :
Message         : The job succeeded.  The Job was invoked by User vader\andyl.  The last step to run was step 1
                  (CommandLog Cleanup).

ComputerName    : vader
InstanceName    : SQL19
SqlInstance     : vader\SQL19
Job             : DatabaseBackup - SYSTEM_DATABASES - FULL
StepName        : (Job outcome)
RunDate         : 2025-05-12 08:23:05
StartDate       : 2025-05-12 08:23:05.000
EndDate         : 2025-05-12 08:23:08.000
Duration        : 3 s
Status          : Succeeded
OperatorEmailed :
Message         : The job succeeded.  The Job was invoked by User vader\andyl.  The last step to run was step 1
                  (DatabaseBackup - SYSTEM_DATABASES - FULL).

This is getting a lot more manageable. But it’s still a lot to read through without some help.

Adding a Visualization

Here’s where the second dbatools function comes into play - ConvertTo-DbaTimeline. This function will convert the objects returned by selected dbatools functions into an HTML chart that shows when each instance of each job ran, how long it took to run, and the results. Then we can pipe the output into Out-File to write a file to disk, and open it up in a web browser.

Let’s look at the history for the past day on the same instance.

1
2
3

$Last24h = Get-DbaAgentJobHistory -SqlInstance localhost\sql19 -StartDate (Get-Date).AddDays(-1);
$Last24h | ConvertTo-DbaTimeline | Out-File -Encoding utf8 -Path "C:\temp\JobHistory.html";
Invoke-Item -Path "C:\temp\JobHistory.html";

Note: The images in this post are fairly large, and have been resized for the main post. Click each to see a larger version

Agent Job History converted to an HTML page

Very nice! We can see when those jobs ran, and even see that there was a failure in the middle of the day. Hovering the mouse over each instance of the job run pops up a tooltip with the details.

dbatools timeline tooltip example

This probably won’t work out for multiple days in a single view if you have a lot of jobs that run many times through the day (like transaction log backups), but for checking individual days it works really well as a visualization.

Because this Get-DbaAgentJobHistory works like most every other dbatools function, I can also pass in multiple instances and collect this data from all of them in one go. Let’s look at just transaction log backups across my three instances.

1
2
3

$Last24h = Get-DbaAgentJobHistory -SqlInstance localhost\sql19,localhost\sql17,localhost\sql22 -StartDate (Get-Date).AddDays(-1) -Job "DatabaseBackup - USER_DATABASES - LOG";
$Last24h | ConvertTo-DbaTimeline | Out-File -Encoding utf8 -Path "C:\temp\LogBackupHistory.html";
Invoke-Item -Path "C:\temp\LogBackupHistory.html";

This time, we can see that the instance name is prefixed on each job name, making it easy to discern which timeline is from which instance.

Multi-instance job history

Next Step Ideas

This looks cool, but what are the practical applications? A couple come to mind immediately.

  • If you’re new to an environment, or even new to an instance, this can be a very quick way to get a picture of what’s running, when, how quickly, and how reliably. Are differential backups taking a long time? Are index rebuilds happening four times a day? Are log backups failing at a particular time?
  • Is there significant overlap in the scheduling and runtime of jobs? May be hard to find by looking at individual schedules, but this representation can make it readily apparent.
  • Do you have a daily dashboard, status meeting, or health check email? This timeline could be rolled into those for a quick visual of how the past 24 hours have gone.