Note to myself: How to easily count the number of Jobs in SQL Server Agent Jobs?

Posted: October 17, 2023  |  Categories: Other SQL Server

This is just another post for the sake of my mental sanity because I’m always tired of looking up for this over and over again. While working on BizTalk Server projects and in many other scenarios, like optimizing the BizTalk Server environment, I want to check how many SQL Server Jobs I have in my SQL Server Instance in order to optimize and properly configure the maximum job history log. This way, I know that none of the jobs is going to be without execution history on the logs, and I can easily monitor and troubleshoot them.

Normally, I have a few SQL Server Jobs, and it is easy to count them manually, nevertheless annoying! But sometimes I get SQL Server Instances hosting applications databases used by BizTalk Server with more than 60 jobs… and counting them manually is not an annoying task but instead an insane task!

So the million dollar question is, How to easily count the number of Jobs in SQL Server Agent Jobs?

To easily count the number of SQL Server Agent Jobs in your SQL Server instance, you can use a simple SQL query. Here’s how you can do it:

FROM msdb.dbo.sysjobs;

This query counts the number of rows in the msdb.dbo.sysjobs table, which contains information about SQL Server Agent Jobs. The result will be the total number of jobs.

Keep in mind that you need the necessary permissions to query the msdb database, which is where SQL Server Agent Jobs are stored. Typically, users with administrative roles or appropriate permissions can access this information.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Leave a Reply

Your email address will not be published. Required fields are marked *


Back to Top