FIX: Assertion error when executing a stored procedure that references a large object in SQL Server 2014, 2016, and 2017

Applies to: SQL Server 2014 EnterpriseSQL Server 2014 Enterprise CoreSQL Server 2014 Developer

Symptoms


An assertion error may occur when Microsoft SQL Server repeatedly executes a stored procedure that performs the following:

  • Takes a large object, such as varchar(max) or varbinary(max), as an argument, and
  • Creates a temporary table that is scoped to the execution of the procedure, and
  • Uses the large object argument in the temporary table.

You may find the assertion error that resembles following in the SQL Server error log:

Date/Time SPID Error: 17065, Severity: 16, State: 1.

Date/Time SPID SQL Server Assertion: File: FilePath\FileName, line = LineNumber Failed Assertion = 'fFalse' Attempt to access expired blob handle (1). 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.

Date/Time SPID Error: 3624, Severity: 20, State: 1.

Date/Time SPID 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.

Cause


SQL Server has internal logic to disable caching of queries that reference large objects so that subsequent executions do not reference those LOBs (which were created during prior runs and therefore are invalid for subsequent runs). That logic did not handle the case of Deferred Name Resolution (DNR) on temporary tables that caused those plans to be cached. Scoped temporary tables are expensive to create, and SQL Server caches them for reuse in subsequent executions. This prevents recompilation of such queries because of schema changes.

Learn more about Deferred Name Resolution.

Resolution


This issue is fixed in the following cumulative updates for SQL Server:

       Cumulative update 8 for SQL Server 2016 SP1  

       Cumulative Update 4 for SQL Server 2017

       Cumulative Update 10 for SQL Server 2014 Service Pack 2

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References


Learn about the terminology Microsoft uses to describe software updates.