Does SQL Server 2008 R2 always fail with a severity level 20 error with certain large text manipulation?

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.

Query:

DECLARE @v varchar(MAX) = REPLICATE(CONVERT(varchar(max),'a'),524289);
SELECT @v = @v FROM(SELECT 1 AS a) AS b;

Result:

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.

SELECT @@VERSION:

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

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.

StackExchange Database Administrators Q#290316, answer score: 7