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.sysjobsAdditionally, 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_idWell, that’s it. I hope just like me, you would find this answer interesting.
More Useful Queries on SQL Server Jobs in msdb
The same msdb tables answer more questions while the Agent is down. msdb.dbo.sysjobhistory keeps the past runs with their status and messages, so you can see when a job last failed. Note that the run date and time are stored as integers, such as 20180429 for the date, so convert them before sorting or comparing.
If the history is empty, the job may never have run, or old rows may have been purged by the history retention settings. And before you start the Agent again, check the enabled column in msdb.dbo.sysjobs, so you know exactly which jobs will begin running on their schedules.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
