4

After updating to First Responders Kit 20180901, I have a job that runs every 15 minutes and calls sp_BlitzFirst:

EXEC [DBA Maintenance].[dbo].[sp_BlitzFirst]
    @OutputDatabaseName = 'DBA Maintenance', 
    @OutputSchemaName = 'dbo', 
    @OutputTableName = 'BlitzFirst',
    @OutputTableNameFileStats = 'BlitzFirst_FileStats',
    @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats',
    @OutputTableNameWaitStats = 'BlitzFirst_WaitStats',
    @OutputTableNameBlitzCache = 'BlitzCache',
    @OutputTableRetentionDays = 10;

After updating the SQL Scripts to First Responders Kit 20180901, the Job keeps failing and when I run the Step SQL manually I get below messages:

. . .
Populating Warnings column
Msg 8134, Level 16, State 1, Procedure sp_BlitzCache, Line 3972
Divide by zero error encountered.
The statement has been terminated.
Populating Warnings column for stored procedures
Msg 8134, Level 16, State 1, Procedure sp_BlitzCache, Line 4047
Divide by zero error encountered.
. . .
Writing results to table.
sp_BlitzCache Finished

I think the error is happening for the sp_BlitzCache CASE lines

CASE 
    WHEN   CompileMemory > 1024 AND ((CompileMemory) / (1 * MaxCompileMemory) * 100.) >= 10. 
    THEN    ', High Compile Memory' 
    ELSE    '' 
END

When I SELECT the values in the ##bou_BlitzCacheProcs table, MaxCompileMemory is either NULL or 0 (Zero).

Anybody else having this issue?

Is there a SQL Setting I need to look at?

1 Answer 1

3

Clearly, without a doubt, that's a bug. You'll want to post it there.

I'm guessing MaxCompileMemory is 0 for you? This is the only division I see,

CASE WHEN CompileMemory > 1024 AND ((CompileMemory) / (1 * MaxCompileMemory) * 100.) >= 10. THEN ', High Compile Memory' ELSE '' END, 3, 200000) 

Update

Filed here and fixed with this patch.

1
  • 2
    I replaced the lines with the following and now it works: CASE WHEN CompileMemory > 1024 AND ((CompileMemory) / (1 * CASE WHEN ISNULL(MaxCompileMemory, 0) = 0 THEN 1 ELSE ISNULL(MaxCompileMemory, 0) END) * 100.) >= 10. THEN ', High Compile Memory' ELSE '' END Sep 6, 2018 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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