Casos do Dia a Dia: Exclusão de campo Text não libera espaço em disco?
Fala pessoal,
Compartilhando mais uma experiência que tive no meu dia a dia de trabalho, após a verificação com a equipe de desenvolvimento da possibilidade de exclusão de 2 campos do tipo text de uma tabela muito utilizada, fui com toda empolgação na minha base de testes verificar o quanto de espaço em disco eu ganharia. Para isso, rodei a sp_spaceused para essa tabela, exclui os 2 campos text e rodei novamente a sp_spaceused.
Para minha surpresa, o espaço em disco utilizado por essa tabela continuava o mesmo. Ah não! É impossível! Como que esses campos não ocupam nenhum espaço no banco?
Vamos visualizar essa situação. Com o script abaixo criamos e populamos uma tabela comum:
|
1 2 3 4 5 6 7 8 9 10 11 |
SET NOCOUNT ON CREATE TABLE Anotacao( Id_Anotacao INT IDENTITY(1,1), Dt_Anotacao DATETIME DEFAULT(getdate()), Ds_Anotacao TEXT, CONSTRAINT PK_Anotacao PRIMARY KEY(Id_Anotacao)) -- Índice clustered GO INSERT INTO Anotacao(Ds_Anotacao) SELECT REPLICATE('A',4000) GO 10000 |
Em seguida, executando o comando:
|
1 |
exec sp_spaceused Anotacao |
Temos o seguinte resultado:
Como podemos ver, a tabela possui mais de 40 MB de dados.
Agora excluímos o campo com o comando abaixo:
|
1 2 |
ALTER TABLE Anotacao DROP COLUMN Ds_Anotacao |
Mais uma vez, executando a sp_spaceused temos o resultado abaixo:
|
1 |
exec sp_spaceused Anotacao |
Como assim? O resultado não mudou?
Nesse momento abri uma thread no fórum do technet e após trocar alguns posts com o Gustavo Aguiar (Blog), obtive uma resposta muito esclarecedora:
“Olá Fabrício,
Antes da exclusão da coluna, o espaço estava alocado e os dados organizados. Se você excluir a coluna, para que o espaço seja imediatamente liberado, o SQL Server teria que reorganizar tudo. Para reorganizar, fatalmente haveria um trabalho de IO envolvido bem como uma possível indisponibilidade. Como o SQL Server não sabe a criticidade da tabela, ele opta por não reorganizar e manter o espaço alocado mesmo após a exclusão da coluna.
O espaço será liberado assim que você promover um REINDEX do índice clustered. Se a tabela não possuir um, será necessário criar e depois removê-lo (a menos que seja SQL Server 2008).”
Pronto, acredito que agora tudo já esteja esclarecido para vocês. Vamos testar?
Basta dar um REBUILD no índice:
|
1 |
ALTER INDEX PK_Anotacao ON Anotacao REBUILD |
Agora, executando a sp_spaceused novamente, temos o seguinte resultado:
Agora podemos visualizar o espaço que ganhamos com a exclusão do campo. O tamanho da tabela diminuiu 40 MB.
Bom, depois da ajuda do Gustavo, verifiquei que no meu caso ganharia 10GB de espaço em disco com a exclusão dos meus 2 campos text. =)
Gostou dessa dica?
Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.
Confira mais experiências do Dia a Dia de um DBA no meu Treinamento de Tarefas do Dia a Dia de um DBA.
Abraços,
Fabrício Lima
MCITP – Database Administrator
Consultor e Instrutor SQL Server
Trabalha com SQL Server desde 2006
Boa!!!
Excelente post !!!!
Alex
http://www.alexberre.com
Valeu Alexandre e Alex…
É isso ai Fabrício! Abraço.
Mas eu ainda acho errada essa informação que o SQL nos informa sobre o tamanho da tabela.
Ele poderia nos dizer que, após a exclusão, existe um espaço reservado de 40mb mas na coluna de dados ele tem que informar o valor real.
Assim saberíamos que nao são os dados daquela tabela que estão consumindo espaço, e sim o espaço já pré-alocado por ela, mesmo após a exclusão dos dados.