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:
SELECT COUNT(*) AS NumberOfJobs
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.
