SQL Server 2016 - Merge statement fails when running db in Simple recovery model.
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 = 'ISOFF (BUFMINLOGGED, mbuf->bstat) || pageModifyType != PageModifyTypeContents || 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.
Upvotes: 20<=-=Jan 12 2017 1:22AM=-=>
In version 13.0.4001.0, the problem persists.<=-=Jan 22 2017 12:45PM=-=>
I believe this has been resolved in SQL 2016 SP1 CU1 specifically KB3205964. :)<=-=Mar 14 2017 2:03PM=-=>
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 © Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
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.<=-=Apr 23 2017 9:00PM=-=>
In version 13.0.4422.0, the problem exists.<=-=May 1 2017 10:14AM=-=>
I am getting this in a MERGE statement with an INSERT, in version 13.0.4001.0 (Enterprise), on a SIMPLE recovery mode db.<=-=Jul 20 2017 4:23AM=-=>
I have seen the same issue on a 2016 SP1 CU3 (184.108.40.206) on a database in SIMPLE recovery.<=-=Jul 28 2017 5:07PM=-=>
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.
Paul Burton commented
This is still an issue in 2016 SP2 CU8