All we need is an easy explanation of the problem, so here it is.
When we run this on our SQL Server 2008 R2 instances, it causes a severity 20 error every time. It does not have that issue on our SQL Server 2016 instances.
DECLARE @v varchar(MAX) = REPLICATE(CONVERT(varchar(max),'a'),524289); SELECT @v = @v FROM(SELECT 1 AS a) AS b;
Location: tmpilb.cpp:3256 Expression: fNoReaderWriterConflict SPID: 90 Process ID: 1576 Location: tmpilb.cpp:3306 Expression: fNoReaderWriterConflict SPID: 90 Process ID: 1576 Msg 3624, Level 20, State 1, Line 4 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 QFE from Technical Support. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
SQLDump0002.txt (from non-production server):
Computer type is Intel(R) Xeon(R) CPU E5430 @ 2.66GHz. Bios Version is DELL - 1 Phoenix ROM BIOS PLUS Version 1.10 2.5.0 8 X64 level 8664, 2 Mhz processor (s). Windows NT 6.1 Build 7601 CSD Service Pack 1. Memory MemoryLoad = 87% Total Physical = 32762 MB Available Physical = 4058 MB Total Page File = 65524 MB Available Page File = 27332 MB Total Virtual = 8388607 MB Available Virtual = 8355194 MB **Dump thread - spid = 0, EC = 0x00000003EF7E5F00
Is this a bug in this version of SQL Server 2008 R2?
- This does produce a set of crash dump related files. The layperson-readable sections of them basically repeat the error result above.
- We recognize SQL Server 2008 R2 is old and out of support. We will be upgrading.
- We have already worked around this issue by setting the large text variable outside of a SELECT statement.
- We were curious if this happened in any other environments and we wanted this publicly documented in case it happens to someone else.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
This reproduces for me on Microsoft SQL Server 2008 R2 (SP2) build 10.50.4000. It seems to have been fixed some time after SP3. The issue was reported on Connect (and subsequently migrated to user voice, so that link works).
You need more than 512KB of data in the LOB, and a variable assignment that reads from the same variable, without performing any calculations.
-- No error at 512KB DECLARE @v varchar(max) = REPLICATE(CONVERT(varchar(max), 'a'), 512 * 1024); SET @v = (SELECT @v); -- Assertion failure at 512KB + 1 DECLARE @v varchar(max) = REPLICATE(CONVERT(varchar(max), 'a'), 512 * 1024 + 1); SET @v = (SELECT @v);
The statement very nearly executes without error. SQL Server performs all the calculations correctly and even assigns @v to @v without issue. The assertion occurs when SQL Server is closing down the statement, writing the final value of @v back out to the supplied variable reference @v. It does this as if @v were a parameter to the assignment statement. If that sounds complex, that’s because it is. Variable assignment is a bit of a hack, with some strange legacy behaviours. I don’t envy those that have to maintain that code with all its quirks.
The assertion does not happen with values 512KB or below because SQL Server uses an in-memory ILockBytes (ILB) class
CInMemIlb to handle the operation. You may have noticed ILB in the assertion message (tmpilb.cpp). Over 512KB, SQL Server uses
CMainIlb instead, which checks for other readers using the blob handle when writing. Assigning @v to itself without modification means this check fails.
Among the many possible workarounds, two that fall naturally out of the above observations are:
- Assign to a different variable
- Perform a calculation
Examples are shown below. Neither produces the erroneous assertion:
-- Assign to a different variable DECLARE @v varchar(max) = REPLICATE(CONVERT(varchar(max), 'a'), 512 * 1024 + 1); DECLARE @v2 varchar(max); SET @v2 = (SELECT @v); -- Perform a calculation DECLARE @v varchar(max) = REPLICATE(CONVERT(varchar(max), 'a'), 512 * 1024 + 1); SET @v = (SELECT @v + '');
Interestingly, the same assertion has resurfaced recently in a SQL Server 2019 fix for query store and polybase.
There’s really no database involved. You can see it hits no tables. But yes we’ve run this "in" several databases and run CHECKDB against them just to be sure.
The database involved is tempdb (database ID 2). That’s where the value of LOB variables gets written. You can see that by looking in the
sys.dm_db_task_space_usage DMV (if your query weren’t failing with an error, that is):
My data database context for that query was "master," but notice that the database_id in the query results in tempdb.
So you could check tempdb for corruption. This database gets recreated each time the server is restarted, so I’d be surprised if you have persistent corruption there. Unless you’re having disk issues, in which case you should consistently see errors in the Windows "System" Event log that indicate corruption.
All that said, it’s also very possible this is a bug in SQL Server 2008 R2 SP3. There is at least one non-security-related hotfix to that SP (KB3033860). The errors mentioned there don’t look like they match your situation, but could be related (they are assertion failures).
Unfortunately, since SQL Server 2008 R2 is out of support, this won’t be fixed even if it is a bug. Your best bet is to try and work around the problem, or upgrade.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂