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.
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.
Note: This script uses Time, Date data types which were introduced in SQL 2008 so this script is not compatible with any prior version of SQL Server.
This script will work on SQL Server 2008, 2008 R2 and 2012.
Sarabpreet Singh Anand
Author : Sarabpreet Singh Anand
E-Mail : sarab<AT>Sarabpreet (dot)com
website : www.sarabpreet.com
To get update on this or all my other scripts\blogs follow me at:
Blog @ http://www.sqlservergeeks.com/blogs/sarab