SQL Server 2016 MERGE Causing A system assertion check has failed

Question

Let me first say that I understand that Microsoft says they have a bug fix, but we've installed the cumulative update for 2016 and still no luck. This is a MERGE for a SCD type 2 that has been running fine in 2014. We are testing our upgrade to 2016 when this error showed up. Below is a sample query that is causing the issue.

/* Type 2 Merge */
INSERT INTO BDW.DMEmployer 
                (
                SCD_Current,
                Source_System,
                Company,
                Employer_Number
                )
SELECT          SCD_Current,
                Source_System,
                Company,
                Employer_Number
FROM
    (
       MERGE BDW.DMEmployer dim
       USING ETLWork.work.DMEmployer work
              ON     dim.Employer_Number = work.Employer_Number AND dim.Company = work.Company
       -- This inserts new records in the dimension table
       WHEN NOT MATCHED THEN
              INSERT (SCD_Current,
                    Source_System,
                    Company,
                    Employer_Number
       VALUES (     'Y',
                    work.last_updated,
                    'I',
                    'POINT',
                    work.Company,
                    work.Employer_Number
       WHEN MATCHED AND dim.SCD_Current = 'Y'
                    AND ( dim.Employer_FEIN <> work.Employer_FEIN ) THEN
               UPDATE SET   SCD_Current='N', 
                            SCD_Valid_To_Date = work.last_updated-2
    
        OUTPUT $Action
                    Action_Taken,                                          
                    'Y' AS SCD_Current,
                    work.Source_System,
                    work.Company,
                    work.Employer_Number
    ) AS MERGE_OUT
WHERE MERGE_OUT.Action_Taken = 'UPDATE';

If you were to only run the inside MERGE, this runs no problem. But trying to run the whole thing now in 2016 causes the below failure. We have tried the bug fix, but I'm wondering if I have missed something else. It's only adding ~150 rows in the outside INSERT INTO portion.

Error Message:

Location: pageref.cpp:955

Expression: IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage ()

SPID: 52

Process ID: 2820

Msg 3624, Level 20, State 1, Line 3 A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.

Msg 10054, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

We have run the DBCC CHECKDB, which turned up nothing being corrupted. So, is this still a bug, or is there some setting that I may have overlooked during our install? Thank you for your help.

Answer 1

In my experience using recovery model = simple sometimes gives this error. Switching to recovery model = full eliminates the problem and has been my workaround, although not an ideal one.