Alterar tipo de dados de alteração de coluna usa armazenamento de versão tempdb?

Supondo que estamos falando de um plain ALTER TABLE, não de uma operação feita por meio de algum assistente que faz tabela temporária e renomeia truques ou algo semelhante.

Uma alteração de tipo de dados de coluna reta pode resultar na atualização de todas as linhas da tabela. Depende dos tipos envolvidos. Você deve considerar o seguinte: a imagem da linha na tabela é apenas um saco de bytes, até que seja de alguma forma interpretada pelo mecanismo. O mecanismo segue a descrição dos metadados da tabela para entender quais são esses bytes. Os metadados dirão 'do deslocamento 12 ao deslocamento 16 é um int, o número da coluna 4. Do deslocamento 17 ao deslocamento 19 é um curto, o número da coluna 5' e assim por diante. Agora, quando você fizer um ALTER que altera um tipo de coluna, os metadados serão alterados. E agora a pergunta de $ 1 milhão: os novos metadados descreverão o mesmo pacote de bytes corretamente? Se sim, a linha ALTER é instantânea e todas as linhas da tabela permanecem inalteradas. Se não, então todoslinhas devem ser atualizadas. Uma tabela enorme levará a uma grande atualização, tudo em uma transação. Esta atualização pode esgotar seus recursos (por exemplo, ficar sem espaço de registro). Shanky está certo de que esta 'atualização' pode assumir a forma de uma tabela temporária->inserir, mas isso é um detalhe de implementação.

Escrevi há algum tempo sobre as colunas da tabela do SQL Server que mostram como algumas dessas operações ocorrem e como elas deixam evidências nos metadados físicos reais do conjunto de linhas.

Agora, você diz que está ficando sem espaço de armazenamento de versão quando esta operação é concluída, mesmo que o instantâneo não tenha sido ativado, certo? O armazenamento de versão é usado por mais do que apenas isolamento de instantâneo. Para dar um exemplo bem conhecido, a pseudotabela DELETED em gatilhos é alimentada pelo armazenamento de versão. O que significa que o armazenamento de versão pode ser usado mesmo que você não habilite o isolamento de instantâneo. Consulte Gerenciando o TempDB no SQL Server: Noções básicas do TempDB (Armazenamento de versão: por que precisamos dele?) , Sunil cita 3 usos não relacionados a instantâneos do armazenamento de versão: gatilhos, MARS e criação de índice online.

O SQL 2014 não oferece suporte à coluna de alteração online e, como uma operação DDL, não deve exigir controle de versão de linha para gatilho nem para MARS. Portanto, IMHO, não deve aumentar o armazenamento de versões.

Minha principal suspeita seria, neste caso, um secundário legível (é um tiro no escuro aqui...). O fato de que os secundários legíveis mapeiam todas as leituras para o isolamento do instantâneo está bem documentado . Não me lembro exatamente dos detalhes e posso estar errado, mas acho que para que o banco de dados secundário ofereça suporte ao isolamento de instantâneo, o primário deve usar o armazenamento de versão. Pense no fato de que o secundário é uma cópia física do primário e o tamanho da linha deve corresponder.

Talvez não seja uma resposta, mas é muito longo para um comentário.

Acredito que o mecanismo db esteja criando uma nova tabela temporária e preenchendo-a. Observar seu plano de execução pode fornecer pistas. Você pode ver a citação no artigo a seguir, mas depois de pesquisar, não vi nada oficial na documentação do MS.

Além disso, se você estiver executando isso usando o designer do SSMS, ele poderá ter um comportamento diferente da execução da consulta de alteração de tabela. Se estiver usando o designer em vez de clicar em salvar, você pode fazer o script da consulta.

https://www.mssqltips.com/sqlservertip/1903/best-practices-for-sql-server-database-alter-table-operations/

A maioria das operações ALTER TABLE, se executadas usando SSMS, causam uma queda e recriação da tabela

Se necessário, eu resolveria isso para que eu mesmo pudesse criar uma nova tabela e bombear os dados para a nova tabela. Se você tiver que deixar essa tabela aberta para gravações durante o processo, ficará mais complicado.

Além disso, você pode fornecer temporariamente 500 GB extras ao TempDB enquanto a operação está em execução. Uma tabela de 250 GB não precisa de mais do que isso, mas já vi coisas estranhas com o TempDB.

Estou citando um parágrafo

Apêndice A: Usando o Management Studio para alterar tipos de dados do documento do SQL Server 2005 sobre o impacto da alteração de agrupamento e tipos de dados

Quando você altera o tipo de dados da coluna, as seguintes coisas acontecem

1. Internamente, o SQL Server cria uma tabela temporária que tem a mesma propriedade de coluna da coluna de destino.

2. Em seguida, uma instrução INSERT INTO é executada para copiar os dados para a tabela temporária.

Quando eles usaram o Profiler, a seguinte consulta foi vista.

IF EXISTS(SELECT * FROM dbo.t1)--T1 was table with col C1 whose data type was changed
   EXEC('INSERT INTO dbo.Tmp_t1 (c1)
      SELECT CONVERT(nchar(10), c1) FROM dbo.t1 WITH (HOLDLOCK TABLOCKX)')

3. Em seguida, o índice é adicionado se já existia na tabela original e o nome da tabela é renomeado para o nome da tabela original.

Agora, considerando que você tem uma tabela muito grande, a tabela temporária também era grande e o Tempdb não foi capaz de acomodá-la e, portanto, ficou cheia.