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

Status : 

 


17
0
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

Description

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