How to List All the SQL Server Jobs When Agent is Disabled? – Interview Question of the Week #171

Question: How to List All the SQL Server Jobs When Agent is Disabled?

Answer: There are some questions, which just blows the mind. I have never thought of this question till I had heard from a developer attending my training asked me.

Before we see the answer to this question, let us understand the origin of this question.

SQL Server Jobs is the folder under SQL Server Agent in SSMS (SQL Server Management Studio). You can see that in the image displayed below.

Now if we disabled SQL Server Agent services, it is not possible to further expand SQL Server Agent node. This limits the users to explore what are the available jobs in SQL Server. You can easily check that from the image displayed below.

I believe here is where the question in the subject have originated. How we can see the list of the jobs when SQL Server Agent is disabled.

There are two different ways to achieve our goal over here.

Method 1: Enable SQL Server Agent services and check the jobs. However, this answer takes the fun away so let us see the more interesting method 2.

Method 2: Using T-SQL

All the details of the jobs are stored in MSDB system database. We can easily write the following script and get the necessary details about all the available jobs in SQL Server.

SELECT *
FROM msdb.dbo.sysjobs

Additionally, we can use the following script to get details about all the ACTIVE jobs and their schedule as well. Please note that even though the jobs are active they may not run if your SQL Server Agent is disabled.

SELECT * 
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobschedules sjs ON sj.job_id = sjs.job_id

Well, that’s it. I hope just like me, you would find this answer interesting.

Reference: Pinal Dave (https://blog.sqlauthority.com)