SQL Swiss Army Knife #8 - Find a Maintenance Window

  • Article

Hello all,
So this another post on SQL scripts for DBAs, following the series "SQL Swiss Army Knife". This time with a way to “guesstimate” (heard this a while ago) the availability window on a server. Note that this is only usable from SQL Server 2005 onwards.

Have you ever needed to find a few *minutes* where you can intervene on a server but you can’t seem the tell when? Or even just a few moments to do a controlled failover? Do you have lots of jobs running on a server and can’t tell when they are overlapping?
If your answer is YES on these questions, this script can help you. I have been using it for awhile (I have shared it in SQLServerCentral a few years ago), and use it to try and find an availability window to work on servers, taking into account all the scheduled jobs and backups on the server.
A few caveats:

  • It averages all the executions of a given job in the last 30 days to estimate the probable job ending, thus the “guesstimation” I referred above.
  • It includes backup times, provided such info is in MSDB.
    • Please note that some 3rd party backup agents DO NOT write info in MSDB when taking backups.
  • Because it operates within the scope of what is perceived as a task (job) in SQL Server, it doesn’t include information from scheduling agents other than SQL Agent.
  • In addition, it’s not aware of application-driven tasks.

The output will have a summary table with the time slot(s) where a batch of overlapping jobs are executing and the available time between slots.
The second part of the output are stats on all jobs currently scheduled on the server, that are used to calculate the summary table. 
It resembles the following:

Check_Availability

Hope you find it useful.

Download code here: Check_Availability.sql

Until next time!

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.