SQL Server Agent Job Monitoring with PowerShell

By: Alejandro Cobar   |   Updated: 2020-06-29   |   Comments (1)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | > Monitoring

Problem

To continue the tip series, this PowerShell script will collect information to build the inventory of SQL Server Agent jobs for all of the servers that have been registered for this monitoring solution.

Solution

This module will gather information about SQL Server Agent jobs from all the specified instances. It will connect to each server instance in the inventory.MasterServerList table and capture the data for each of these instances.

The connection to each SQL Server instance will be based on the settings in the inventory.MasterServerList table.

Prerequisites

In order to use this module, you need to create the core objects found here.

Database Objects

For this particular module, only one table will be created and it is the table to store the information of the SQL Server Agent jobs from each instance.

This is the structure of the table, so you can get an idea of what data will be stored. (If you want to add more fields to this table, make sure to adjust the structure within the PowerShell script and adapt the respective logic that will handle the additional columns.)

Tables

• inventory.Jobs- this will store information about SQL Agent Jobs for all servers
  • serverId - serverid ties back to inventory.MasterServerList
  • job_name - name of SQL Agent job
  • is_enabled - if job is enabled
  • owner - owner of the job
  • date_created - when job was created
  • date_modified - when job was last changed
  • frequency- how often job is run
  • days - how often job is run
  • execution_time- last execution time of the job
  • data_collection_timestamp - when data was last collected

PowerShell Script

The PowerShell script that creates the above object and inserts data into the inventory.Jobs table is called:

• Get-MSSQL-Instance-Jobs.ps1.

The script includes validations that will help you check if some key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered to be able to have something to work with.

The PowerShell script should be stored in the centralized location. For this series we have been using "C:\Temp".

If you want to change the working directory and use something other then "C:\temp" you will need to edit the PowerShell script and modify the first line as shown below. This will be the case for all future modules too.

How to Use

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

Option 1

• Right click on Get-MSSQL-Instance-Jobs.ps1 and select Run with PowerShell.

Option 2

• Open a command window and navigate to the folder where you saved the above files and run:

powershell "C:\temp\Get-MSSQL-Instance-Jobs.ps1"

Option 3

• Schedule this as a SQL Agent job to run the PowerShell script on a set basis.

Option 4

• Schedule this as a Windows Task Scheduler job to run the PowerShell script on a set basis.

Check Creation of Database and Objects

After running the PowerShell script, we can see the new object created inventory.Jobs.

If we query the inventory.Jobs table, we can see the data that has been collected.

Important note: the PowerShell script will store only the information from the very last execution. If you’d like to keep information from previous executions, you will have to modify the script and adapt it to your particular use case.

Checking for Errors

To check for errors query the monitoring.ErrorLog table using the following query:

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-Jobs'

If you’d like to include the SQL Server instance that had errors, issue the query like this:

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE 
   CONCAT(msl.server_name,'\',msl.instance) 
   END AS instance,  
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Get-MSSQL-Instance-Jobs'

Useful Queries

By collecting all the data related to the jobs across all your instances, you might answer things like below.

Find jobs that include Backup

What’s the schedule backups across all the instances. Here we are looking for the keyword "Backup" in the job name.

SELECT
   CASE WHEN msl.instance = 'MSSQLSERVER' 
   THEN msl.server_name 
   ELSE CONCAT(msl.server_name,'\',msl.instance) 
   END AS instance, 
   j.job_name,
   j.frequency,
   j.days,
   j.execution_time
FROM inventory.Jobs j
JOIN inventory.MasterServerList msl ON j.serverId = msl.serverId
WHERE j.job_name LIKE '%Backup%'

Find jobs where owner is not sa

I would like to know which jobs have owners other than sa. Here we are looking at the owner column.

SELECT
   CASE WHEN msl.instance = 'MSSQLSERVER' 
   THEN msl.server_name ELSE         
   CONCAT(msl.server_name,'\',msl.instance) 
   END AS instance,
   j.job_name,
   j.owner
FROM inventory.Jobs j
JOIN inventory.MasterServerList msl ON j.serverId = msl.serverId
WHERE j.owner <> 'sa'

Download Scripts

• Download the scripts for this module.

Next Steps

• Download the PowerShell script to collect data for SQL Server Agent Jobs.
• Check out the other parts of this series
  • Monitoring SQL Server with PowerShell Core Object Setup
  • Monitoring SQL Server with PowerShell Instance Data Collection

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips