Inventory SQL Server Agent Jobs for all Instances with PowerShell

By:   |   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.

list of instances

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:

powershell script output

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:

data collection error results
  • 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:

list of all sql server agent jobs

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!


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Alejandro Cobar 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


Comments For This Article




Tuesday, November 19, 2019 - 8:58:13 PM - Alejandro Cobar Back To Top (83141)

Absolutely yes, you can accomplish the exact same thing with dba-tools. I'm just showing 1 more way, as I geniunly believe that having different options to accomplish a certain task never hurts ;)

Friday, October 25, 2019 - 5:10:22 PM - James Back To Top (82901)

Couldn't all this be done with dba-tools?

Friday, October 25, 2019 - 7:33:01 AM - Dave Rogers Back To Top (82894)

Have you heard of the dbatools PowerShell module? It is endorsed by Microsoft employees and MVPs as being a great way of using PowerShell to maintain SQL Server. The Get-DbaAgentJob cmdlet could answer all 3 of your questions and it is only one of 287 dbatools cmdlets!

Tuesday, October 8, 2019 - 7:01:55 AM - Fiodar Hulin Back To Top (82694)

I did similar thing to collect job info from 20+ SQL servers

initially I made csv file collecting all SQL-setvers here

Import-Csv .\in_server.csv | foreach {   
Write-Host  $_.name
...

check connection ( some were resttricted )

and read infofrom msdb database

SELECT 
@@SERVERNAME server ,  SERVERPROPERTY ('ProductVersion') as version
, a.name as job_name -- , a.category_id  
, replace(  replace( replace( a.description  , char(13) , '')  , char(10) , '')  , 'No description available.' ,'') 
as job_description 
, CAST( a.date_modified as date) as job_modified
, a.originating_server_id as Ismulti
, cat.name as category -- , sch.enabled 
, sch.name as sched_name -- , sch.freq_type 
, sch.active_start_time , sch.active_end_time
, st1.cnt_step
, z2.avg_run_minute as [AVG run (minutes)]
, z2.cnt_run [Кол-во запусков]
FROM  msdb..sysjobs a
LEFT JOIN   msdb..syscategories cat
       ON cat.category_id = a.category_id
LEFT JOIN   msdb..sysjobschedules j_s
       ON j_s.job_id = a.job_id
LEFT JOIN   msdb..sysschedules sch
    ON j_s.schedule_id =  sch.schedule_id

LEFT JOIN   
(
    Select  job_id ,  count(*) as cnt_step
    from msdb.dbo.SYSJOBSTEPS
    group by job_id
) st1
     on st1.job_id = a.job_id
LEFT JOIN   
(
Select  job_id , AVG( (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60 ) as avg_run_minute ,   count(*) as cnt_run
from msdb.dbo.SYSJOBHISTORY
where step_name = '(Job outcome)'
group by job_id
) z2
  on z2.job_id = a.job_id
WHERE 
a.enabled = 1
and sch.enabled = 1
ORDER BY 1 ,2,3,4

then insert

into csv ( then convert to Excel )  for ALL servers

Friday, October 4, 2019 - 9:16:27 AM - Max Turavani Back To Top (82666)

Hi,
I like your script except there is a bug that will cause SECTION #7 to fail if the job name has embedded quotes in it. I resolved it by changing the $jobsInventoryQuery variable to:

 ##SECTION 5 BEGIN
#Let's prepare the query that will fetch the jobs for the instances
$jobsInventoryQuery = "
SET QUOTED_IDENTIFIER OFF;
SELECT 
   replace(j.name,""'"",""''"") name,
   j.enabled,
   SUSER_SNAME(j.owner_sid) AS owner,
   j.date_created,
   j.date_modified
FROM msdb.dbo.sysjobs j
SET QUOTED_IDENTIFIER ON;
"
##SECTION 5 END

Works perfectly after this modification!
Thanks
Max














get free sql tips
agree to terms