SQL Server Agent Job Monitoring with PowerShell
By: Alejandro Cobar | Updated: 2020-06-29 | Comments | Related: 1 | 2 | 3 | 4 | More > Monitoring
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.
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.
In order to use this module, you need to create the core objects found here.
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.)
- 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
The PowerShell script that creates the above object and inserts data into the inventory.Jobs table is called:
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:
- Right click on Get-MSSQL-Instance-Jobs.ps1 and select Run with PowerShell.
- Open a command window and navigate to the folder where you saved the above files and run:
- Schedule this as a SQL Agent job to run the PowerShell script on a set basis.
- 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'
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 the PowerShell script to collect data for SQL Server Agent Jobs.
- Check out the other parts of this series
Last Updated: 2020-06-29
About the author
View all my tips