{"id":613,"date":"2022-06-22T22:00:41","date_gmt":"2022-06-23T03:00:41","guid":{"rendered":"https:\/\/brentec.ca\/blog\/?p=613"},"modified":"2022-06-29T22:54:18","modified_gmt":"2022-06-30T03:54:18","slug":"t-sql-script-to-show-all-disabled-sql-server-agent-schedules-that-have-jobs","status":"publish","type":"post","link":"https:\/\/brentec.ca\/blog\/2022\/06\/t-sql-script-to-show-all-disabled-sql-server-agent-schedules-that-have-jobs\/","title":{"rendered":"T-SQL script to show all disabled SQL Server Agent schedules that have jobs"},"content":{"rendered":"<p style=\"font-size: 16px; letter-spacing: 1px;\"><strong><span style=\"color: #3366ff;\">Problem<\/span>:<\/strong> SQL Server Agent jobs on your server are enabled and have at least one schedule assigned to them, but they do not run at the scheduled date\/time.<\/p>\n<p style=\"font-size: 16px; letter-spacing: 1px;\"><span style=\"color: #3366ff;\"><strong>Why?<\/strong><\/span> There could be a number of reasons, but one that is easily overlooked is because an underlying schedule is disabled.<\/p>\n<p style=\"font-size: 16px; letter-spacing: 1px;\">The following script when run on your SQL Server instance will show all schedules that are disabled but more importantly will show those schedules that have jobs associated with them.\u00a0This is important because it will help identify those jobs that may not run because the schedule has been disabled. The last thing any concerned DBA will want is a job that has been scheduled but will not run because the underlying schedule is not functional.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">select schedule_id, &#x5B;name]\r\nfrom msdb.dbo.sysschedules\r\nwhere enabled = 0 and schedule_id in\r\n(select distinct schedule_id from msdb.dbo.sysjobschedules)<\/pre>\n<p style=\"font-size: 16px; letter-spacing: 1px;\"><strong><span style=\"color: #3366ff;\">Explanation<\/span>:<\/strong> The MSDB table <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-tables\/dbo-sysschedules-transact-sql?view=sql-server-ver16\" target=\"_blank\" rel=\"noopener\">dbo.sysschedules<\/a> contains information on each individual schedule within the agent. Additionally, the MSDB table <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-tables\/dbo-sysjobschedules-transact-sql?view=sql-server-ver16\" target=\"_blank\" rel=\"noopener\">dbo.sysjobschedules<\/a> contains schedule information for jobs that are to be scheduled by the agent.<br \/>\nIf the job_id is in dbo.sysjobschedules, but the underlying schedule is disabled, the job will not run. This could pose a potential problem area in your environment.<\/p>\n<p style=\"font-size: 16px; letter-spacing: 1px;\">Did you find this useful? Let me know in the comments below. Cheers.<\/p>\n<p style=\"font-size: 16px; letter-spacing: 1px;\"><strong><span style=\"color: #3366ff;\">Note<\/span>:<\/strong> this blog post was originally created on January 15, 2015.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Problem: SQL Server Agent jobs on your server are enabled and have at least one schedule assigned to them, but they do not run at the scheduled date\/time. Why? There could be a number of reasons, but one that is easily overlooked is because an underlying schedule is disabled. The following script when run on &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/brentec.ca\/blog\/2022\/06\/t-sql-script-to-show-all-disabled-sql-server-agent-schedules-that-have-jobs\/\"> <span class=\"screen-reader-text\">T-SQL script to show all disabled SQL Server Agent schedules that have jobs<\/span> Read More &raquo;<\/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":"","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\/613"}],"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=613"}],"version-history":[{"count":33,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/613\/revisions"}],"predecessor-version":[{"id":652,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/613\/revisions\/652"}],"wp:attachment":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/media?parent=613"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/categories?post=613"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/tags?post=613"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}