{"id":688,"date":"2022-07-25T19:00:24","date_gmt":"2022-07-26T00:00:24","guid":{"rendered":"https:\/\/brentec.ca\/blog\/?p=688"},"modified":"2022-07-25T07:59:47","modified_gmt":"2022-07-25T12:59:47","slug":"t-sql-script-showing-sql-server-agent-jobs-that-have-never-run","status":"publish","type":"post","link":"https:\/\/brentec.ca\/blog\/2022\/07\/t-sql-script-showing-sql-server-agent-jobs-that-have-never-run\/","title":{"rendered":"T-SQL script showing SQL Server agent jobs that have never run"},"content":{"rendered":"

Background<\/strong><\/span>: 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.
\nFor 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<\/a>, so users can determine if they have any “waste” in their agent instances too.
\nThough 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<\/a>.<\/p>\n

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.<\/p>\n

\r\nselect sj.[name], sjs.*\r\nfrom msdb.dbo.sysjobservers sjs\r\njoin msdb.dbo.sysjobs sj\r\non sjs.job_id = sj.job_id\r\nwhere sjs.last_run_date = 0\r\n  and sjs.last_run_time = 0\r\n  and sjs.last_run_duration = 0\r\norder by sj.[name]\r\n<\/pre>\n
Output<\/strong><\/span>: 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.<\/p>\n
\"\"<\/p>\n
Explanation<\/strong><\/span>: There are some key takeaways from the output of the query. Firstly, the last_run_outcome<\/em> 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,\u00a0last_run_date<\/em>, last_run_time<\/em> and last_run_duration<\/em> 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.<\/p>\n
External Links<\/strong><\/span>: The MSDB table\u00a0dbo.sysjobs<\/a> stores the information for each scheduled jobs to be executed by the SQL Server Agent. Additionally, the MSDB table dbo.sysjobservers<\/a> 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<\/em> column in msdb.dbo.sysjobservers table and can be cross-referenced to the dbo.sysservers<\/a> table. A server_id = 0 is the local instance.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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, …<\/p>\n
  T-SQL script showing SQL Server agent jobs that have never run<\/span> Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[3,56,45],"tags":[],"_links":{"self":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/688"}],"collection":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/comments?post=688"}],"version-history":[{"count":33,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/688\/revisions"}],"predecessor-version":[{"id":736,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/688\/revisions\/736"}],"wp:attachment":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/media?parent=688"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/categories?post=688"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/tags?post=688"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}