{"id":645,"date":"2022-06-29T19:00:22","date_gmt":"2022-06-30T00:00:22","guid":{"rendered":"https:\/\/brentec.ca\/blog\/?p=645"},"modified":"2022-06-29T22:54:39","modified_gmt":"2022-06-30T03:54:39","slug":"t-sql-script-to-show-sql-server-agent-jobs-that-are-enabled-but-do-not-have-a-schedule","status":"publish","type":"post","link":"https:\/\/brentec.ca\/blog\/2022\/06\/t-sql-script-to-show-sql-server-agent-jobs-that-are-enabled-but-do-not-have-a-schedule\/","title":{"rendered":"T-SQL script to show SQL Server agent jobs that are enabled but do not have a schedule"},"content":{"rendered":"<p style=\"font-size: 16px; letter-spacing: 1px;\"><span style=\"color: #3366ff;\"><strong>Problem<\/strong><\/span>: SQL Server Agent jobs on your server are enabled 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, one of which could be because the underlying schedule is not enabled as we&#8217;ve seen <a href=\"https:\/\/brentec.ca\/blog\/2022\/06\/t-sql-script-to-show-all-disabled-sql-server-agent-schedules-that-have-jobs\/\">in this post<\/a>. However, some jobs may not run (even though they are enabled) because they have no schedule assigned to the job at all.<\/p>\n<p style=\"font-size: 16px; letter-spacing: 1px;\">The following script can be run on your SQL Server instance to determine which jobs are enabled but do not have a schedule assigned to them.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">select *\r\n\u00a0\u00a0from msdb.dbo.sysjobs\r\n\u00a0\u00a0where enabled = 1\r\n\u00a0\u00a0and job_id not in (select job_id from msdb.dbo.sysjobschedules)\r\n<\/pre>\n<p style=\"font-size: 16px; letter-spacing: 1px;\"><span style=\"color: #3366ff;\"><strong>Explanation<\/strong><\/span>: If the job_id is not in the dbo.sysjobschedules table but the underlying job is enabled within dbo.sysjobs, the job will still not run automatically, this is by design. One can however manually run the job if desired via the system stored procedure in the MSDB database, dbo.sp_start_job.<\/p>\n<p style=\"font-size: 16px; letter-spacing: 1px;\"><span style=\"color: #3366ff;\"><strong>External Links<\/strong><\/span>: The MSDB table <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-tables\/dbo-sysjobs-transact-sql?view=sql-server-ver16\">dbo.sysjobs<\/a> stores the information for each scheduled jobs to be executed by the SQL Server Agent. Additionally, the MSDB table\u00a0<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. The MSDB system stored procedure <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-stored-procedures\/sp-start-job-transact-sql?view=sql-server-ver16\">dbo.sp_start_job<\/a>, instructs SQL Server Agent to start a job immediately.<\/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 August 20, 2014.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Problem: SQL Server Agent jobs on your server are enabled but they do not run at the scheduled date\/time. Why? There could be a number of reasons, one of which could be because the underlying schedule is not enabled as we&#8217;ve seen in this post. However, some jobs may not run (even though they are &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/brentec.ca\/blog\/2022\/06\/t-sql-script-to-show-sql-server-agent-jobs-that-are-enabled-but-do-not-have-a-schedule\/\"> <span class=\"screen-reader-text\">T-SQL script to show SQL Server agent jobs that are enabled but do not have a schedule<\/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\/645"}],"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=645"}],"version-history":[{"count":8,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/645\/revisions"}],"predecessor-version":[{"id":668,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/645\/revisions\/668"}],"wp:attachment":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/media?parent=645"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/categories?post=645"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/tags?post=645"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}