Blog: How Trace Flag 1254 Fixes SQL Server Error Message 3628 with Queries Using In-Memory Tables

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.

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 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:

DBCC TRACEON( 1254, -1)

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.

Using SQL Server Configuration Manager to add trace flag 1254 fixes SQL Server Msg 3628

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.

Share this...
Share on Facebook
Tweet about this on Twitter
Share on LinkedIn