I recently ran into this issue with SQL Server 2019 CTP 2.5 where I was getting the error – Msg 3628, Level 16, State 1, Line 4 The Database Engine received a floating point exception from the operating system while processing a user request. Try the transaction again. If the problem persists, contact your system administrator. It turned out that this would happen whenever I ran a query that joined In-Memory tables with disk based tables and the result ended up with no rows returned. Here is how trace flag 1254 fixes SQL Server Msg 3628 floating point exceptions.
What causes Msg 3628, Level 16, State 1, Line 4 The Database Engine received a floating point exception
Here is an example of one of our queries that causes this problem.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT f.fileID, f.DocID, ftm.ReportTypeID, ftm.TableName AS raw_table, ftm.TableNameDWH AS stage_table FROM [cfg].[files] f INNER JOIN [cfg].[documents] d ON f.DocID=d.DocID INNER JOIN [cfg].[files_to_tables_mapping] ftm ON ftm.FileToTableMapID=f.FileToTableMapID LEFT JOIN log.[dwh_import_info] lii ON f.FileID=lii.FileID AND ftm.ReportTypeID=lii.ReportID AND lii.StatusID IN (0,1) WHERE d.StatusID=0 AND lii.id IS NULL AND isnull(ftm.TableName,'')!='' AND isnull(ftm.TableNameDWH,'')!='' AND (ftm.TableName LIKE 'Speedwell%' OR ftm.TableName LIKE 'ICE%') ORDER BY 3,4,5; |
The tables [cfg].[files] and [cfg].[documents] are In-Memory tables and the others are disk based row-store tables.
The fix with SQL Server trace flag 1254
After a search of the internet, I found the blog post from one of my all time trusted sources of all things SQL Server Brent Ozar called What If Week: What Would You Add? The fix itself was buried in the comments section from one of the users that indicated that there was an undocumented global trace flag T-1254 that would fix the issue.
Sure enough, when I added the following trace flag like this:
1 2 | DBCC TRACEON( 1254, -1) GO |
The problem went away.
I went ahead and added the trace flag to the SQL Server Startup Parameters for my instance using Configuration Manager to make sure the flag gets set the next time someone needs to restart the instance.
I am not aware of any side effects and I’ll see what I can dig up from my former colleagues on the SQL Server team about this flag. On the plus side, I don’t see these messages in the SQL Server errorlog.
