SQL Server Agent Job Monitoring with PowerShell


By:   |   Updated: 2020-06-29   |   Comments   |   Related: 1 | 2 | 3 | 4 | More > 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.

powershell script parameters

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.

database objects

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

query results

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

Next Steps


Last Updated: 2020-06-29


get scripts

next tip button



About the author





Comments For This Article





download


Recommended Reading

Monitoring SQL Server with PowerShell Core Object Setup

Monitoring SQL Server with PowerShell Instance Data Collection

SQL Server Backup Monitoring with PowerShell

How to setup SQL Server alerts and email operator notifications

Reading the SQL Server log files using TSQL





get free sql tips
agree to terms


Learn more about SQL Server tools