search

marcingminski / sqlwatch

SQL Server Performance Monitor
https://docs.sqlwatch.io
Other
436 stars 171 forks source link

usp_sqlwatch_internal_process_actions - @last_action_time (snapshot_time) in UTC, compared against non-UTC GETDATE() #443

Open seanwhitent opened 2 years ago

seanwhitent commented 2 years ago

Describe the bug @last_action_time, which comes from the most recent snapshot_value in [dbo].[sqlwatch_logger_check_action] for an action, is stored in UTC time. In the stored procedure usp_sqlwatch_internal_process_actions, to determine if it should retrigger the action, compares this UTC time against the (potentially) non-UTC time of GETDATE() in lines 169 and 170 in the datediff:

when @check_status <> 'OK' and last_check_status = @check_status and (@action_repeat_period_minutes is not null and datediff(minute,isnull(@last_action_time,'1970-01-01'),getdate()) > @action_repeat_period_minutes) then 'REPEAT'

For my time zone in UTC-7, the datediff returns a negative integer. This means that even though action_repeat_period_minutes is set to 1, action_hourly_limit is set to 60, and action_every_failure is set to 1, it will not repeat the action every minute.

Expected behavior A row in [sqlwatch_config_check_action] with action_repeat_period_minutes is set to 1, action_hourly_limit is set to 60, and action_every_failure is set to 1 will have its associated action trigger every minute when the corresponding check is not "OK".

Screenshots image

Windows Server (please complete the following information):

SQL Server (please complete the following information):

SQL Server Management Studio (SSMS -> about -> copy info):

SQLWATCH version (from DACPAC or from sysinstances)

marcingminski commented 2 years ago

Hi, did you try version 4.3? And if yes - does the problem still exist?