Background: Some individuals like to keep jobs in their SQL Server Agent for future code reference; these jobs have no schedule and will never run. For those I say, put them into your favorite code repository, not only for you but for others on your team too.
For the SQL Server instances that I manage, I like to keep them as “clean” as possible. I don’t see the need to keep jobs in the system that have never run or will never run. This is why I originally incorporated this metric within SQL Agent Insight, so users can determine if they have any “waste” in their agent instances too.
Though my original monstrosity of a query is within the code of the app (I’m not going to showcase it here), I wanted to share what I used. However, in realizing how out-of-date and inefficient it was (at least 300 lines of T-SQL, man that is embarrassing), I’ve refined it to what you see below which will be incorporated into a future version of SQL Agent Insight.
With that, below is the T-SQL that will help identify those agent jobs that have never run so they can be purged or migrated to your code repo of choice.
select sj.[name], sjs.* from msdb.dbo.sysjobservers sjs join msdb.dbo.sysjobs sj on sjs.job_id = sj.job_id where sjs.last_run_date = 0 and sjs.last_run_time = 0 and sjs.last_run_duration = 0 order by sj.[name]
Output: When run in Microsoft’s SQL Server Management Studio, you can expect the output to be similar to the following showing the job name from the msdb.dbo.sysjobs table and all of the columns from the msdb.dbo.sysjobservers table.
Explanation: There are some key takeaways from the output of the query. Firstly, the last_run_outcome column having a value greater than 4 means that the agent does not know the state of the job. However, a default value of 5 is used when a job is created for the first time. And secondly, to determine that a job has never run, all 3 columns, last_run_date, last_run_time and last_run_duration should equal zero (0). If those columns show anything different, the job in question has run before. The job history may not show it, but the sysjobservers table has a record of it.
External Links: The MSDB table dbo.sysjobs stores the information for each scheduled jobs to be executed by the SQL Server Agent. Additionally, the MSDB table dbo.sysjobservers stores the association or the relationship between a particular job and one or more target servers. These servers are distinguishable by referencing the server_id column in msdb.dbo.sysjobservers table and can be cross-referenced to the dbo.sysservers table. A server_id = 0 is the local instance.
Did you find this useful? Do you have a better way to achieve the desired result using T-SQL? Let me know in the comments below. Cheers.