O SQL Server 2008 R2 sempre falha com um erro de nível de gravidade 20 com certa manipulação de texto grande?

Não há realmente nenhum banco de dados envolvido. Você pode ver que não atinge nenhuma mesa. Mas sim, executamos isso "em" vários bancos de dados e executamos CHECKDB neles apenas para ter certeza.

O banco de dados envolvido é tempdb (ID do banco de dados 2). É aí que o valor das variáveis ​​LOB é escrito. Você pode ver isso olhando no sys.dm_db_task_space_usageDMV (se sua consulta não estiver falhando com um erro, ou seja):

Meu contexto de banco de dados de dados para essa consulta era "mestre", mas observe que database_id na consulta resulta em tempdb.

Assim, você pode verificar se há corrupção no tempdb. Esse banco de dados é recriado toda vez que o servidor é reiniciado, então eu ficaria surpreso se você tivesse corrupção persistente lá. A menos que você esteja tendo problemas de disco, nesse caso você deverá ver consistentemente erros no log de eventos "Sistema" do Windows que indicam corrupção.

Dito isso, também é muito possível que seja um bug no SQL Server 2008 R2 SP3. Há pelo menos um hotfix não relacionado à segurança para esse SP ( KB3033860 ). Os erros mencionados não parecem corresponder à sua situação, mas podem estar relacionados (são falhas de afirmação).

Infelizmente, como o SQL Server 2008 R2 está sem suporte, isso não será corrigido, mesmo que seja um bug. Sua melhor aposta é tentar contornar o problema ou atualizar.

Isso se reproduz para mim no Microsoft SQL Server 2008 R2 (SP2) build 10.50.4000. Parece ter sido corrigido algum tempo depois do SP3. O problema foi relatado no Connect (e posteriormente migrado para a voz do usuário, para que o link funcione).

Você precisa de mais de 512 KB de dados no LOB e uma atribuição de variável que leia da mesma variável, sem realizar nenhum cálculo.

Por exemplo:

-- 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);

A instrução quase executa sem erro. O SQL Server executa todos os cálculos corretamente e até atribui @v a @v sem problemas. A asserção ocorre quando o SQL Server está fechando a instrução, gravando o valor final de @v de volta na referência de variável fornecida @v. Ele faz isso como se @v fosse um parâmetro para a instrução de atribuição. Se isso soa complexo, é porque é. A atribuição de variável é um pouco hack, com alguns comportamentos legados estranhos. Não invejo aqueles que têm que manter esse código com todas as suas peculiaridades.

A asserção não ocorre com valores de 512 KB ou abaixo porque o SQL Server usa uma classe ILockBytes (ILB) na memória CInMemIlbpara manipular a operação. Você deve ter notado ILB na mensagem de asserção (tmpilb.cpp). Mais de 512 KB, o SQL Server usa CMainIlbem vez disso, que verifica outros leitores usando o identificador de blob ao gravar. Atribuir @v a si mesmo sem modificação significa que esta verificação falha.

Entre as muitas soluções possíveis, duas que se enquadram naturalmente nas observações acima são:

1. Atribuir a uma variável diferente
2. Faça um cálculo

Exemplos são mostrados abaixo. Nenhum dos dois produz a afirmação errônea:

-- 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 + '');

Curiosamente, a mesma afirmação ressurgiu recentemente em uma correção do SQL Server 2019 para repositório de consultas e polybase.