SQL Server 2016 - Merge statement fails when running db in Simple recovery model. - by kiwipeet

Status : 


Sign in
to vote
ID 3005568 Comments
Status Active Workarounds
Type Bug Repros 5
Opened 8/2/2016 2:54:58 PM
Access Restriction Public


My ETL process is failing when I try and load data into my fact table using a SQL merge statement. We are using SSIS which calls a SQL task containing a SQL merge statement.  The exact same setup and ETL process works in SQL 2012 Standard but not SQL 2016 Developer. 

Full error text: DESCRIPTION:	SQL Server Assertion: File: <pageref.cpp>, line=955 Failed Assertion = 'IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage ()'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

I am running Microsoft SQL Server 2016 (RTM-CU1) (KB3164674) - 13.0.2149.0 (X64)   Jul 11 2016 22:05:22   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)  Running on VMWare. 

This seems to be the same as a known (and fixed) error in earlier versions. https://support.microsoft.com/en-nz/kb/2297709

The same workaround applies. If I change the db into full recovery model the error doesn't occur.  
Sign in to post a comment.
Posted by Microsoft on 7/28/2017 at 5:07 PM
Starting SQL Server 2016, we have enabled fast inserts by default while bulk loading data into heap or clustered index. This is part of perf optimization where if the if yodatabase is in simple or bulk logged recovery mode and u try to bulk load (bulk insert, insert..select, select into, merge with insert..select) we use minimal logging automatically for all newly inserted records to optimize bulk load performance. When using minimal logging mode, we need to flush the buffers immediately as we only log the pages and extents. Before the pages is flushed, if the page buffer is accessed again as part of the same merge operation, we hit access violation.

To avoid the issue, you can use trace flag 692 which disables fast inserts and reverts back the behavior older version of SQL Server. Alternatively, if feasible, you can also switch to full recovery model which disable minimal logging.

We have documented trace flag 692 in our documentation below and will be supported by Microsoft.


Posted by Henrik Sjang Davidsen on 7/20/2017 at 4:23 AM
I have seen the same issue on a 2016 SP1 CU3 ( on a database in SIMPLE recovery.
Posted by xhead on 5/1/2017 at 10:14 AM
I am getting this in a MERGE statement with an INSERT, in version 13.0.4001.0 (Enterprise), on a SIMPLE recovery mode db.
Posted by Scott Nicholls RGIS on 4/23/2017 at 9:00 PM
In version 13.0.4422.0, the problem exists.
Posted by _-AC-_ on 3/22/2017 at 2:50 PM
Can confirm we're running SQL Server 2016 Standard Edition with CU1 (13.0.4411.0.) and experiencing this issue.

Setting to FULL RECOVERY model has stopped this from happening but is obviously only a short term solution.
Posted by -Quinny- on 3/14/2017 at 2:03 PM
We have this issue on SQL Server 2016 Standard Edition with CU1 (13.0.4411.0.). Applying the workaround (set DB to FULL recovery) works – the SSIS packages run without issue.

KB3205964 states that the bug was fixed and applied to SQL 2016 Developer, Enterprise and Enterprise Core editions. We are on Standard edition so this looks to be a variation.

The affected SQL Server was an in-place upgrade from SQL Server 2014 Standard to SQL Server 2016 Standard as follows:
- Original build was SQL Server 2014 (SP2-CU2-GDR) (KB3194718) - 12.0.5532.0 (X64)
- Performed in-place upgrade to SQL Server 2016 with SP1 (13.1.4001.0)
- Applied CU1 for SQL Server 2016 SP1 (13.0.4411.0)

Current build of the SQL Server is:

Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64)
Jan 6 2017 14:24:37
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
Posted by kiwipeet on 1/22/2017 at 12:45 PM
I believe this has been resolved in SQL 2016 SP1 CU1 specifically KB3205964. :)
Posted by Eltrin on 1/12/2017 at 1:22 AM
In version 13.0.4001.0, the problem persists.