Tabela truncada de 200 GB, mas espaço em disco não liberado

Se você estiver referenciando o consumo real de arquivos de banco de dados no volume, o SQL Server não lidará com isso automaticamente . Só porque você removeu dados do banco de dados não significa que os arquivos do banco de dados serão reduzidos para caber apenas nos dados existentes.

O que você estaria procurando, se precisar recuperar espaço no volume, seria reduzir o arquivo específico com DBCC SHRINKFILE. Vale a pena observar algumas práticas recomendadas, conforme essa documentação:

Melhores Práticas

Considere as seguintes informações ao planejar reduzir um arquivo:

• Uma operação de redução é mais eficaz após uma operação que cria muito espaço não utilizado, como uma tabela truncada ou uma operação de eliminação de tabela.

• A maioria dos bancos de dados requer algum espaço livre disponível para operações regulares do dia-a-dia. Se você reduzir um banco de dados repetidamente e perceber que o tamanho do banco de dados aumenta novamente, isso indica que o espaço que foi reduzido é necessário para operações regulares. Nesses casos, reduzir repetidamente o banco de dados é uma operação desperdiçada.

• Uma operação de redução não preserva o estado de fragmentação dos índices no banco de dados e geralmente aumenta a fragmentação até certo ponto. Esse é outro motivo para não reduzir repetidamente o banco de dados.

• Reduza vários arquivos no mesmo banco de dados sequencialmente em vez de simultaneamente. A contenção nas tabelas do sistema pode causar atrasos devido ao bloqueio.

Também digno de nota:

DBCC SHRINKFILEas operações podem ser interrompidas em qualquer ponto do processo e qualquer trabalho concluído é retido.

Certamente há algumas coisas a serem consideradas ao fazer isso, e eu recomendo que você dê uma olhada no post do blog de Paul Randal sobre o que acontece quando você faz essa operação.

O primeiro passo definitivamente seria verificar quanto espaço e espaço livre você realmente pode substituir, bem como o espaço usado no(s) arquivo(s):

use AdventureWorks2012;
go

;with db_file_cte as
(
    select
        name,
        type_desc,
        physical_name,
        size_mb = 
            convert(decimal(11, 2), size * 8.0 / 1024),
        space_used_mb = 
            convert(decimal(11, 2), fileproperty(name, 'spaceused') * 8.0 / 1024)
    from sys.database_files
)
select
    name,
    type_desc,
    physical_name,
    size_mb,
    space_used_mb,
    space_used_percent = 
        case size_mb
            when 0 then 0
            else convert(decimal(5, 2), space_used_mb / size_mb * 100)
        end
from db_file_cte;

Este é um comportamento normal ao truncar a tabela e que envolve a remoção de mais de 128 extensões conforme os Manuais Online

Quando você descarta ou recria índices grandes, ou descarta ou trunca tabelas grandes, o Mecanismo de Banco de Dados adia as desalocações de página reais e seus bloqueios associados até que uma transação seja confirmada. Essa implementação oferece suporte a transações explícitas e de confirmação automática em um ambiente multiusuário e se aplica a tabelas e índices grandes que usam mais de 128 extensões.

O Mecanismo de Banco de Dados evita os bloqueios de alocação necessários para descartar objetos grandes dividindo o processo em duas fases separadas: lógica e física.

Na fase lógica, as unidades de alocação existentes usadas pela tabela ou índice são marcadas para desalocação e bloqueadas até que a transação seja confirmada. Com um índice clusterizado que é descartado, as linhas de dados são copiadas e, em seguida, movidas para novas unidades de alocação criadas para o armazenamento, seja um índice clusterizado reconstruído ou um heap. (No caso de uma reconstrução de índice, as linhas de dados também são classificadas.) Quando há uma reversão, somente esta fase lógica precisa ser revertida.

A fase física ocorre após a confirmação da transação. As unidades de alocação marcadas para desalocação são descartadas fisicamente em lotes. Essas quedas são tratadas dentro de transações curtas que ocorrem em segundo plano e não exigem muitos bloqueios.

Como a fase física ocorre após a confirmação de uma transação, o espaço de armazenamento da tabela ou índice ainda pode aparecer como indisponível. Se esse espaço for necessário para que o banco de dados cresça antes da conclusão da fase física, o Mecanismo de Banco de Dados tentará recuperar espaço das unidades de alocação marcadas para desalocação. Para localizar o espaço atualmente usado por essas unidades de alocação, use a exibição de catálogo sys.allocation_units.

As operações de descarte adiadas não liberam o espaço alocado imediatamente e introduzem custos indiretos adicionais no Mecanismo de Banco de Dados. Portanto, tabelas e índices que usam 128 ou menos extensões são descartados, truncados e reconstruídos exatamente como no SQL Server 2000. Isso significa que as fases lógica e física ocorrem antes da confirmação da transação.

Você teria que esperar e, é claro, teria que reduzir manualmente o arquivo para recuperar espaço também vale a pena mencionar que a redução causa fragmentação lógica e deve ser evitada, a menos que sua necessidade seja grave. Você teria que equilibrar de alguma forma entre o encolhimento e a fragmentação. É melhor se perguntar se a redução realmente resolveria o problema, considerando o cenário em que os dados voltarão a crescer de qualquer maneira.

Use a consulta abaixo para verificar quanto espaço livre existe no banco de dados

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Além das respostas de Tom e Shanky, se seu banco de dados contiver dados LOB/BLOB, o DBCC SHRINKFILE pode não funcionar. Se for esse o caso, você tem duas opções, dependendo se pode colocar o banco de dados offline ou não. Se você puder colocar o banco de dados offline, precisará copiar os dados e copiá-los de volta para remover o espaço vazio. Você pode fazer isso por um dos seguintes:

1. Usando uma instrução SELECT INTO para transferir a tabela inteira para uma nova tabela. Elimine a tabela original, execute DBCC SHRINKFILE . Renomeie a nova tabela para o nome da tabela original.
2. Usando bcp para copiar a tabela no modo nativo, elimine a tabela, execute DBCC SHRINKFILE , crie tabela e, em seguida, use bcp os dados na tabela.
3. Usando Exportar/Importar para mover todos os dados para um novo banco de dados, elimine o banco de dados existente, renomeie o novo banco de dados para o nome do banco de dados original.

Se você não puder colocar o banco de dados offline, poderá usar o comando DBCC SHRINKFILE com a opção EMPTYFILE .

Detalhes para cópia offline: http://support.microsoft.com/kb/324432/en-us

Informações atuais para a opção EMPTYFILE http://msdn.microsoft.com/en-us/library/ms189493(v=sql.105).aspx

Para entender melhor por que o SQL não recupera espaço automaticamente quando você exclui dados, pense nos arquivos de dados do SQL Server como uma unidade de autoarmazenamento comercial:

Os dados de tabelas e índices são organizados nessas unidades (chamadas "páginas"), e o SQL Server é muito meticuloso em acompanhar o que pertence a onde.

Se você encher todas as unidades de armazenamento, poderá comprar terrenos e construir mais (expandindo o arquivo de dados), mas o que acontece quando algumas unidades são esvaziadas?

• Se a unidade vazia estiver no final da fila, com certeza, você pode demolir a unidade que construiu e vender o terreno, mas isso dá muito trabalho, especialmente se você precisar comprá-lo novamente.
• Mas se a unidade vazia estiver no meio da fileira de unidades de armazenamento, você não poderá fazer isso sem muito trabalho extra. Você teria que mover todas as coisas das unidades cheias no final da linha para as unidades vazias no meio antes de demolir as unidades e vender a propriedade.

Não só está movendo todas essas unidades em torno de muito trabalho (desnecessário), mas também pode causar fragmentação de tabela e índice , o que pode afetar o desempenho.

Imagine se você tivesse uma família que comprasse 10 unidades uma ao lado da outra e, mais tarde, você os forçasse a mover 4 dessas unidades para unidades vazias aleatórias em outro local da instalação. Quando eles vierem adicionar ou remover coisas de suas unidades, eles terão que fazer muito trabalho extra vagando entre as unidades espalhadas.

Obviamente, essa analogia não é perfeita e, claramente, há situações em que você realmente deseja reduzir um arquivo de dados, mas não deseja que o SQL Server faça isso sozinho, deseja controlar quando e como exatamente isso acontece.

Por exemplo, você provavelmente gostaria de fazer um DBCC SHRINKFILEpara um tamanho específico, deixando uma parte do espaço livre interno e, em seguida, seguir imediatamente por uma reindexação/desfragmentação de suas tabelas/índices, para corrigir qualquer fragmentação que você acabou de criar.

Moral desta história: Nunca ligue o AutoShrink!