SQL Server Agent Job Activity

SQL Server Agent provides a graphical user interface to inspect the status of agent jobs. The Job Activity Monitor looks like:

However, if you want to programmatically obtain the detail about running jobs, including details like when running jobs started and how long they’ve been running, etc, there is no concise way to do that.

I’ve built a query that can be used to obtain those details, and more. The code obtains details from the agent-related tables, views, and stored procedures in the msdb database, along with details from the session-related system dynamic management views.

For help deciding when to schedule SQL Server Agent Jobs, you might look for gaps between existing jobs.