LearnSQL

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:

1_Find_Jobs_scheduled_to_run_during_a_specific_time_range_SQL_Server

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

Enjoy!

Regards

Sarabpreet Anand