28 Jan

Find Jobs scheduled to run during a specific time range

Hi Friends,

Today I was going to raise a change which was related to SAN Migration for one of our production servers. The affected server will not be available for almost 3 hours. I prepared the plan and asked Mr. X to look into this & execute the change during the weekend. Mr. X happily agreed to it, I asked him to go through the plan and clear the doubts if any.

After reviewing the plan he asked multiple question which I answered promptly. His last question was “Sarab, you’ve mentioned that we need to execute the daily jobs manually which are scheduled to run between 2:30 AM to 4:30 AM CST. But there are 48 Odd jobs on this server; do you want me to check each and every job manually?”

This question by Mr. X forced me to complete & document this long pending query (which I always wanted to document somewhere but I was lingering it due to Laziness)

So I wrote the complete query & documented it so that everyone can make use of this query.

Here is the link for the query: http://gallery.technet.microsoft.com/Find-Jobs-scheduled-to-run-d565fdfa/file/68524/1/Find%20Jobs%20scheduled%20to%20run%20during%20a%20specific%20time%20range.sql

The logic is pretty simple, the only challenge I faced was to change the time and date which is given in non-standard format.

To change the time I created a Function (also available with this script) which can change the time from 15500    to 01:55:00 which is more readable in nature.

This query gives the capability to filter\find the enabled jobs on the server which are scheduled to be executed between a certain time ranges.

You can also tweak the query to include the date parameter in where clause.

Here is the example output:


Do let me know your inputs on the same. – Just leave a quick comment.



Sarabpreet Anand

About Author:

SQLServer - MVP Sarabpreet is SQLServer MVP, Consultant, Speaker, Trainer, Blogger and Community Lead. You can find him speaking at a local UG Event or a SQL Webcast. He has 11+ years of Experience and worked with Industry Leaders. He has many SQL Certifications under his belt. His core competency lies in administration of SQL Server. Always ready to help, online \offline. His life’s mantra is “Knowledge Increases by sharing so, Pass it on”.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.