Arithmetic overflow error when using sp_Blitz

Question

I am getting this error:

Msg 8115, Level 16, State 2, Procedure sp_Blitz, Line 469 Arithmetic overflow error converting expression to data type int.

when running sp_Blitz in a server with 52 user databases. I also get it when I run it with @CheckUserDatabaseObjects = 0.

I have the latest version from 10/14/2016.

Any ideas what I am doing wrong or what over parameters I should run this with since there are so many user databases?

• paulbarbin: Have you looked at line 469 (or around there) for the offending query?

Answer 1

There was an issue with the latest release, shown in ISSUE 526. It was updated, by changing this code on line 472.

FROM

SELECT @MsSinceWaitsCleared = 
DATEDIFF(MINUTE, create_date, CURRENT_TIMESTAMP) * 60000

TO

SELECT @MsSinceWaitsCleared = 
DATEDIFF(MINUTE, create_date, CURRENT_TIMESTAMP) * 60000.0

If you do a new Pull request through github it has the fix in it.

• James Rhoat: Don't forget to hit the checkbox if this was the solution :)

Answer 2

I had incurred the same error message and found this page. I double-checked to ensure that I had the newest version. I checked the code fix as stated above but continued to get an arithmetic overflow. Version 5.4 VersionDate 20270603 I actually found the largest INT one can use in milliseconds is :2146999999

Upon looking at the code deeper and checking against the tables what I found is that the error was being generated during the insert into #BlitzResults (Line 536). The DATEADD function can not handle the size in milliseconds for a server that has been online for a length of time or has not been reset. Simple change:

FROM:

'Someone ran DBCC SQLPERF to clear sys.dm_os_wait_stats at approximately: ' + CONVERT(NVARCHAR(100), DATEADD(ms, (-1 * @MsSinceWaitsCleared), GETDATE()), 120))

TO:

'Someone ran DBCC SQLPERF to clear sys.dm_os_wait_stats at approximately: ' + CONVERT(NVARCHAR(100), DATEADD(DD, (-1 * @MsSinceWaitsCleared/3600000/24), GETDATE()), 120))

Once I converted the variable from milliseconds to "days" no more overflow. You can also use HH as your interval, merely remove the last "/24" from conversion. The difference is only about an hour. I tested with milliseconds equaling 1 day to milliseconds equaling 7 to 8 months.