By: Alejandro Cobar | Updated: 2019-10-04 | Comments (5) | Related: > SQL Server Agent
Problem
If your manager or a colleague asked you:
- Can you tell me how many SQL Server Agent Jobs we have in all of our instances?
- Out of all the SQL Server Agent Jobs, can you tell me which and how many are disabled/enabled?
- Can you tell me who's the owner of the SQL Server Agent Jobs on all of our instances?
Would you be able to easily provide answers for those questions and provide an answer in a very short period of time? If your answer is no, then this tip is for you. I will give you a tool that will allow you to provide answers for these questions (and perhaps some more).
Solution
The purpose of this tip is to present you a PowerShell script that builds (within every single execution) a centralized inventory of all the SQL Server Agent Jobs from all the SQL Server instances under your care.
Code Explained
The process requires that you have a compiled list of servers under your care. You are free to choose the way to feed the PowerShell script with the list of instances to traverse (e. g. a separate text file with such list), but in this tip I'm going to use a table that I have populated with such information.
##SECTION 1
- The script can work with the parameter $populateInstances (which by default
is set to 0).
- When 0, it means that you already populated the instances table and won't be doing so within the script.
- When 1, it means that the script will populate the instances table for you, which require you to enter them in ##SECTION 3.
- The $server and $inventoryDB values should be modified to specify your SQL Server instance and database where you will be centralizing the information collected.
##SECTION 2
- In this section, 3 tables will be created for you, in case they don't
already exist.
- The first one is where the instances list will be stored, and name of the table is and should be (for the correct behavior of the script) instances.
- The second one is where the results fetched from each instance will be stored.
- The third one is an error log table to capture any error encountered while trying to fetch the results from each individual instance.
##SECTION 3
- In this section, the $populateInstances parameter is evaluated and if you
enter 1 then it will attempt to insert the respective instance registers in
the instances table.
- Please make sure to replace the XXXX with the values to fit your use case (feel free to add more parenthesis if you need).
##SECTION 4
- In this section, the list of instances to traverse is specified and execute. After the list has been retrieved from the central SQL Server instance, it will be stored within the $instances variable.
##SECTION 5
- This section contains the T-SQL code that will retrieve the jobs information for the instance.
##SECTION 6
- In this section, the "instances_jobs_inventory" table is truncated before collecting any data from any instance. That way you don't pile up information with each run, at least that's not how it is designed right now, but feel free to accumulate the information if you like or just comment out or delete this section and you're good to go.
##SECTION 7
- Prior to attempting to do anything, the table "instances_jobs_inventory" is TRUNCATED so that results obtained within each script execution are not accumulated over time. If you wish to accumulate results over time, for whatever reason, feel free to comment or delete this section.
- This section contains the main mechanics that performs the work, using the
following high-level approach:
- For each instance found in the instances table, apply the T-SQL from ##SECTION 5.
- Within a try/catch block, the connection to the instance is made and the information is fetched from it.
- If there's an error while trying to connect to the instance, or if there's an error with the query itself being passed, then such error will be logged in the table called "error_log" so that you can perform the respective troubleshooting later. If an error is encountered, then that particular instance will be skipped, and the script will continue with the rest without interrupting its execution.
- Finally, the respective INSERT statement that is dynamically built during the loop is applied and the information can be viewed in the target table at the end.
PowerShell Script to Build Inventory of All SQL Server Agent Jobs or All Servers
##SECTION 1 BEGIN
param(
$populateInstances = 0
)
#This is where the data will be fetched and stored, so update it according to your case
$server = "Localhost"
$inventoryDB = "master"
##SECTION 1 END
##SECTION 2 BEGIN
#Create the central table where all the instances to traverse will be stored
$masterServerListTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'instances') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE instances(
[instance] [VARCHAR](32) NOT NULL PRIMARY KEY
) ON [PRIMARY]
END
"
Invoke-Sqlcmd -Query $masterServerListTableCreationQuery -Database $inventoryDB -ServerInstance $server
$resultsTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'instances_jobs_inventory') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE instances_jobs_inventory(
[instance] [VARCHAR](32) NOT NULL,
[job_name] [VARCHAR](64) NULL,
[is_enabled] [TINYINT] NULL,
[owner] [VARCHAR](16) NULL,
[date_created] [DATETIME] NULL,
[date_modified] [DATETIME] NULL,
[data_collection_timestamp] [DATETIME] NOT NULL
) ON [PRIMARY]
END
"
Invoke-Sqlcmd -Query $resultsTableCreationQuery -Database $inventoryDB -ServerInstance $server
$errorLogTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'error_log') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE error_log(
[script] [VARCHAR](32) NOT NULL,
[instance] [VARCHAR](32) NOT NULL,
[message] [VARCHAR](MAX) NOT NULL,
[error_timestamp] [DATETIME] NOT NULL
) ON [PRIMARY]
END
"
Invoke-Sqlcmd -Query $errorLogTableCreationQuery -Database $inventoryDB -ServerInstance $server
##SECTION 2 END
##SECTION 3 BEGIN
#If you choose to populate the instances table, please complete this query text accordingly by replacing XXXX with the name of each instance.
#Note: Feel free to add more parenthesis if you need to suit your case (these are just for reference only).
#If you choose not to, then the script assumes that the instances table is already populated to do its thing.
if($populateInstances -eq 1){
$insertInstancesQuery = "
INSERT INTO instances VALUES
('XXXX'),
('XXXX'),
('XXXX')
"
Invoke-Sqlcmd -Query $insertInstancesQuery -Database $inventoryDB -ServerInstance $server
}
##SECTION 3 END
##SECTION 4 BEGIN
#Fetch all the instances from the list you specify
$instanceLookupQuery = "
SELECT instance
FROM instances
"
$instances = Invoke-Sqlcmd -Query $instanceLookupQuery -Database $inventoryDB -ServerInstance $server
##SECTION 4 END
##SECTION 5 BEGIN
#Let's prepare the query that will fetch the jobs for the instances
$jobsInventoryQuery = "
SELECT
j.name,
j.enabled,
SUSER_SNAME(j.owner_sid) AS owner,
j.date_created,
j.date_modified
FROM msdb.dbo.sysjobs j
"
##SECTION 5 END
##SECTION 6 BEGIN
#Let's truncate the InventoryJobs table so that you can always have the latest information only, without accumulating it with each execution
Invoke-Sqlcmd -Query "TRUNCATE TABLE instances_jobs_inventory" -Database $inventoryDB -ServerInstance $server
##SECTION 6 END
##SECTION 7 BEGIN
#Let's perform the lookup for each instance
foreach ($instance in $instances){
#Go grab the information for the instance
Write-Host "Fetching Jobs information from instance:" $instance.instance
try{
$resultsJobsInventory = Invoke-Sqlcmd -Query $jobsInventoryQuery -ServerInstance $instance.instance -ErrorAction Stop -querytimeout 30
$insertQuery = "INSERT INTO instances_jobs_inventory VALUES "
foreach($resultJobsInventory in $resultsJobsInventory){
#Build the respective insert statement
$insertQuery += "('"+$instance.instance+"','"+
$resultJobsInventory['name']+"',"+
$resultJobsInventory['enabled']+",'"+
$resultJobsInventory['owner']+"','"+
$resultJobsInventory['date_created']+"','"+
$resultJobsInventory['date_modified']+"',"+
"GETDATE()"+
"),"
}
#Apply the insert in the central table
Invoke-Sqlcmd -Query $insertQuery.Substring(0,$insertQuery.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
}
catch {
Write-Host -ForegroundColor White -BackgroundColor Red "Error while trying to connect to instance" $instance.instance "please check the error_log table..."
$query = "INSERT INTO error_log VALUES('Instances Jobs Inventory','"+$instance.instance+"','"+$_+"',GETDATE())"
Invoke-Sqlcmd -Query $query -ServerInstance $server -Database $inventoryDB
}
}
##SECTION 7 END
Write-Host "Done!"
In my case I have 3 instances, so initially my instances table will look like below. I have entered an instance called "Localhost2", which doesn't exist, that will be used to show what happens when the script can't make a connection to a valid instance.
Since I already have the instances table populated, the script will be executed with the $populateInstances parameter set to 0, and this is what you will see in the console:
As you can see, all the results were successfully fetched from each valid instance and the error was correctly reported for the "invalid" one. If you take a look at the error log table, will look like this:
- The first column gives you a hint to know which script logged the error, in case multiple different scripts are running in your environment and use the exact same table.
- The second column tells you the name of the instance with the issue, at least it tells you the name of the instance supplied to the script that throws the error.
- The message column contains the actual error message thrown by SQL Server, for troubleshooting purposes.
- The error_timestamp column helps you keep track of when the error was reported.
Here's the results table after being populated by the script:
I created a few test jobs, across all 3 instances, to demonstrate that it works. I intentionally created the job "test3" in a disabled state to have one of this kind captured.
Answering the Questions
As promised in the beginning of this tip, you can now provide the answers to the hypothetical queries like this:
Can you tell me how many jobs do we have in all of our instances?
SELECT COUNT(*) FROM [instances_jobs_inventory];
Out of all the jobs, can you tell me which and how many are disabled/enabled?
SELECT COUNT(*) AS Enabled FROM [instances_jobs_inventory] WHERE is_enabled = 1; SELECT COUNT(*) AS Disabled FROM [instances_jobs_inventory] WHERE is_enabled = 0
Can you tell me who's the owner of the jobs in all of our instances?
SELECT instance, job_name, owner FROM [instances_jobs_inventory]
Next Steps
- You can automate the execution of this script to have the inventory up-to-date. Perhaps a weekly execution can be more than enough.
- With this information, another PowerShell script could be crafted to remotely
enable/disable a job in a particular instance. Perhaps this could be useful
if you have to disable all the jobs within a particular instance, for whatever
reason, and want to save some time.
- I know this can be done through SSMS with almost no sweat, but I'm just presenting an additional/different way to accomplish it. So, expect a complementary script in the very near future.
- In a future tip, I'm going to complement this information with the last execution status of each job, so stay tuned!
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
Article Last Updated: 2019-10-04
