sp_BlitzWho throws "The datediff function resulted in an overflow" sometimes

[jeffmosu]

Version of the script
8.13 2023-02-15 00:00:00.000
(Current version per https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_BlitzWho.sql#L36)

What is the current behavior?
We are logging sp_BlitzFirst to tables with a scheduled job:

EXEC sp_BlitzFirst @OutputDatabaseName = 'DBAtools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzFirst', @OutputTableNameFileStats = 'BlitzFirst_FileStats', @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats', @OutputTableNameWaitStats = 'BlitzFirst_WaitStats', @OutputTableNameBlitzCache = 'BlitzCache', @OutputTableNameBlitzWho = 'BlitzWho', @OutputTableRetentionDays = 30;

Once or twice per day, the job fails. Relevant job output:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart. [SQLSTATE 22003] (Error 535)

If the current behavior is a bug, please provide the steps to reproduce.
You may have to run this SQL repeatedly until rows are returned:

select * from sys.dm_exec_sessions where last_request_start_time = '1900-01-01 00:00:00.000'

What is the expected behavior?
Expected behavior is no failures.

Possible Underlying Cause
The column last_request_start_time seems to default to "1900-01-01" sometimes.

(Screenshot shows relevant columns only. Other columns were NULL or 0. I was logging to a #temp table, which got cut off in the screenshot.)

That column is part of several DATEDIFF calculations:

DATEDIFF(SECOND, s.last_request_start_time, GETDATE())

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
select @@Version

Microsoft SQL Server 2019 (RTM-CU18-GDR) (KB5021124) - 15.0.4280.7 (X64) Jan 23 2023 12:37:13 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Other Background
Looks like Erland Sommarskog had a similar issue in the past with his own software:

2015-02-01 Fixed an overflow issue that Chris Canuel ran into. It appears that when a process just has been created the columns login_time and last_batch_request_time can be 1900-01-01, which caused the computation of last_since to overflow. I'm now taking precautions so that there is a NULL in this column in this situation.

https://www.sommarskog.se/sqlutil/beta_lockinfo.html

Thank you for this software!

[BrentOzar]

Thanks for the bug report. I haven't been able to reproduce this, but I put a quick check in there that may alleviate it. Can you test the current dev branch in your environment to verify that it removes the problem? Otherwise I'm worried that I didn't actually fix it since I can't reproduce it.

If the problem still occurs on your end, can you give me the code change that fixes it? Thanks!

[jeffmosu]

I'm sorry for my late response here. I implemented the fix in our environment. So far, so good! Thank you for providing the patch.

[jeffmosu]

Success! A record with this issue was finally inserted to our BlitzWho logging table. The elapsed_time column is null as expected, and the scheduled job did not fail as before.

Thank you again for the patch and for this software!

[BrentOzar]

My pleasure, glad I could help!