SQLUndercover / UndercoverToolbox

A collection of cool and useful tools, procedures and scripts for the discerning DBA
https://sqlundercover.com
MIT License
84 stars 40 forks source link

Inspector - Data Collection , schedules missing #303

Open markwdavies opened 2 years ago

markwdavies commented 2 years ago

Missing Data collections

If the Inspector Collection Script is not run every minute it can miss scheduled modules. This is due to the way the View [Inspector].[ModuleSchedulesDue] only retrieves collections due in the current minute

To Reproduce

Set the Powershell Inspector script to run every 15 minutes starting on the hour , if a module's schedule is set to frequency 60 mins starting 5 past the hour, only the initial collection will happen , subsequent runs will be missed.

To Fix

This can be resolved by amending the final condition of the where clause in the view [Inspector].[ModuleSchedulesDue] currently it checks "if no run has occurred (NULL) OR if the last run is before the start time for today"

This needs to be extended to include any scheduled times missed since the last run which can be done by amending it as follows :

OR (LastRunDateTime IS NULL OR LastRunDateTime < Schedules.StartDatetime OR LastRunDateTime < DATEADD(MINUTE,-Frequency,DATEADD(MINUTE,RowNum,Schedules.StartDatetime)) ))