Do you run SQL Agent Jobs in your environment? Do you know what is running at any given time? Most people do not know what is currently running if you ask them.
There are several times I need to know what job(s) is running on which server. For instance, let’s say on the morning of monthly maintenance you want to check to make sure all your important ETL jobs are complete or that there are currently no database backup jobs running.
You could run a CMS query across all production servers. However, my preferred method is using the
Get-DbaRunningJob command from the amazing PowerShell module dbatools.
This function works by connecting to multiple SQL servers to get a list of running jobs. I am sure you guessed that by the name of the function.
How I connect to many servers
$servers = Get-DbaRegisteredServer -SqlInstance "localhostsql2017" -Group "SDLCPRD"
This will load the
$servers variable with all the production servers stored in my Central Management Server.
Once you have the server list perform your job status check.
Get a list of running jobs
Get-DbaRunningJob -SqlInstance $servers | ogv?Get-DbaRunningJob -SqlInstance $servers | Where-Object SqlInstance -ne 'DISTSQL01' | ogv
Here you see I have the command twice. My first run returned all running jobs including all SQL replication jobs. That does not really help find what I am looking for. So the second command excluded the SQL distribution server from returning agent jobs.
The results
Now, as you can see, I have three jobs currently running across my 74 production servers. I can either wait to start my maintenance once the jobs complete or I can stop them manually. Decide what best fits you scenario.