Reivindicando espaço em disco após remover o campo da tabela

Decidimos excluir esse campo e todos os seus valores: Existe uma maneira de excluir o campo ntext e todos os seus valores e liberar espaço sem remover a indexação, sem encolher, sem perder o desempenho do banco de dados?

Eu recomendaria usar (de BOL :)

DBCC CLEANTABLE
(
    { database_name | database_id | 0 }
    , { table_name | table_id | view_name | view_id }
    [ , batch_size ]
)
[ WITH NO_INFOMSGS ]

DBCC CLEANTABLE recupera espaço depois que uma coluna de comprimento variável é descartada. Uma coluna de comprimento variável pode ser um dos seguintes tipos de dados: varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant e xml. O comando não recupera espaço depois que uma coluna de comprimento fixo é descartada.

!! CUIDADO !! ( use um tamanho de lote cuidadoso - é aconselhável usar este parâmetro se sua tabela for enorme) :

DBCC CLEANTABLE é executado como uma ou mais transações. Se um tamanho de lote não for especificado, o comando processa toda a tabela em uma transação e a tabela é bloqueada exclusivamente durante a operação . Para algumas tabelas grandes, o comprimento da única transação e o espaço de log requerido podem ser muito grandes. Se um tamanho de lote for especificado, o comando será executado em uma série de transações, cada uma incluindo o número especificado de linhas. DBCC CLEANTABLE não pode ser executado como uma transação dentro de outra transação.

Esta operação é totalmente registrada.

Uma simples reprodução provará que DBCC CLEANTABLEé melhor do que ENCOLHIR (e não se preocupe com a fragmentação :-)

-- clean up
drop table dbo.Test

-- create test table with ntext column that we will drop later
create table dbo.Test (
    col1 int
    ,col2 char(25)
    ,col3 ntext
    );

-- insert  1000 rows of test data
declare @cnt int;

set @cnt = 0;

while @cnt < 1000
begin
    select @cnt = @cnt + 1;

    insert dbo.Test (
        col1
        ,col2
        ,col3
        )
    values (
        @cnt
        ,'This is a test row # ' + CAST(@cnt as varchar(10)) + 'A'
        ,REPLICATE('KIN', ROUND(RAND() * @cnt, 0))
        );
end

--drop the ntext column
ALTER TABLE dbo.Test DROP COLUMN col3 ;

--reclaim the space from the table
-- Note that my table is only having 1000 records, so I have not used a batch size
-- YMMV .. so find a maintenance window and you an appropriate batch size 
-- TEST TEST and TEST before implementing in PROD.. so you know the outcome !!
DBCC CLEANTABLE('tempdb', 'dbo.Test') ;

Para a maioria das partes, estou fazendo referência à série de blogs Inside the storage engine, de Paul Randall.

A única maneira de recuperar espaço não utilizado de arquivos de banco de dados no SQLServer é usando o comando DBCC SHRINK, que realoca dados nos arquivos de banco de dados, liberando páginas e, após removê-los do mapa Global Allcation, remove-os do arquivo de banco de dados. Essa operação é lenta, cria fragmentação no banco de dados e é ainda mais lenta ao lidar com páginas LOB, pois elas são armazenadas como listas vinculadas nos arquivos do banco de dados.

Como você está descartando a coluna NTEXT, terá que aguardar o processo de limpeza fantasma para descartar os dados antes de encolher.

Agora, ter muito espaço livre nos arquivos do banco de dados não fará mal a você, se você tiver espaço em disco, a compactação de backup cuidará do espaço livre nos arquivos.

Se você realmente deseja tornar os arquivos menores, pode criar um novo grupo de arquivos com o banco de dados e torná-lo o padrão e, em seguida, mover as tabelas para o novo grupo de arquivos, mas isso pode levar tempo e causar inatividade. Usei a técnica explicada aqui por Bob Pusateri com bons resultados.

Você deseja reduzir os arquivos do banco de dados porque precisa desse espaço para outros bancos de dados/arquivos que não são do banco de dados ou porque está tendo problemas com este banco de dados ficando sem espaço?

Se for o segundo, talvez você não tenha um problema tão grande quanto pensa. Se estou correto, seu problema é quando o banco de dados precisa crescer para ganhar espaço adicional para novos dados. Depois de remover a coluna, todo o espaço ocupado por essa coluna será liberado para que novas linhas sejam adicionadas às tabelas no banco de dados. Isso significa que levará mais tempo até que seu banco de dados precise crescer. Nesse meio tempo, eu conseguiria algum espaço adicional para sua unidade de dados. A maioria dos bancos de dados cresce com o tempo e é bom ter uma boa margem de espaço livre na unidade.

Eu criaria uma tabela espelho sem a coluna incorreta, copiaria todos os dados para esta tabela, descartaria o original e então renomearia a tabela espelho.