1
  • We have a logging table that has entry added every minute (log_date field in the table is logged in UTC, the server local time is in PST) and we have a condition that monitors and alerts if there is no entry in the table in last 15 minutes.
  • Logging table is logged with data using a SQL Agent job scheduled to run every minute.
  • When the DST is turned off we have received false alerts, how to handle this situation while checking last 15 minutes data?
  • We are currently on SQL Server 2019 and the log_date is of type datetime2.

enter image description here

Added table definition and query used to populate the table using SQL agent job.

create table latency_log(
        [log_date] [datetime2](0) NOT NULL,
        [replica_id] [uniqueidentifier] NULL,
        [log_send_queue_size] [bigint] NULL,
        [log_send_rate] [bigint] NULL,
        [redo_queue_size] [bigint] NULL,
        [redo_rate] [bigint] NULL
)

insert into latency_log
select
    GETUTCDATE() as log_date,
    ar.replica_id,
    hdrs.log_send_queue_size,
    hdrs.log_send_rate,
    hdrs.redo_queue_size,
    hdrs.redo_rate
from sys.dm_hadr_database_replica_states hdrs 
join sys.availability_replicas ar on hdrs.replica_id = ar.replica_id

Condition that monitors the logging:

declare @since datetime2 = dateadd(MINUTE,-15,getutcdate())
if exists ( 
    select top 1 1 
    from latency_log
    where log_date > @since
)
    select 'No alert'
else
    select 'Alert'

I don't find any runs missing from the SQL agent job history.

8
  • 1
    You said that log_date is in UTC, but the screenshot shows log_date being affected by a DST time shift. UTC is doesn't change for DST. Are you sure that column is UTC?
    – AMtwo
    Nov 9 '21 at 0:21
  • The logging is happening via SQL Server Agent job, where the local time zone is PST. I will add that information to my original question Nov 9 '21 at 0:28
  • So, the log_date column is not UTC. Which contradicts this point in your question: "log_date field in the table is logged in UTC"
    – AMtwo
    Nov 9 '21 at 0:41
  • the type of the field is defined as datetime2, but the value being inserted to it is by calling getutcdate() function. Nov 9 '21 at 0:45
  • 1
    "we have a condition that monitors and alerts" you'll also need to post this code. Nov 9 '21 at 6:26
2

I believe that this post by Paul Randal explains the issue you are seeing:

Well, it’s not so common knowledge that at the end of DST when the clocks go back an hour (always at 02:00 in the U.S.), SQL Agent essentially pauses for an hour (in at least SQL Server 2000 onward).

It suggests that, if you check the job history, you will see a gap of an hour when the clocks go back.

1
  • Thank you for the pointer, but I don't find any miss when looked at agent job history. Added complete job run history for that day to my original post. Nov 9 '21 at 22:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.