Entendo o Shrink no SQL Server 2008 (pt-BR)

O Shrink é um recurso que nos permite reduzir o tamanho dos arquivos do banco de dados. Esta operação pode ser feita em conjunto (banco de dados inteiro), ou em um arquivo específico (dados ou log).

O grande problema no uso desse recurso é o mito criado sobre sua funcionalidade.

Como funciona o SHRINK no arquivo de dados

O shrink é um recurso que podemos usar para mover as páginas do arquivo de dados do SQL Server do final do arquivo para o começo, podendo após esse processo, devolver o espaço economizado para o sistema operacional fazer o que quiser com ele, inclusive alocar para outro serviço ou arquivo.

O comportamento natural do SQL Server em uma tabela HEAP é alocar as páginas aleatóriamente no disco permitindo assim maior velocidade nas inserções dos dados.

Isso siginifica que os dados de uma tabela HEAP ficam espalhados no disco, até que um índice Clusterizado seja criado.

Observe as figuras abaixo.

Os dados das tabelas distribuidos no disco antes do SHRINK

http://www.sqlinsiders.net/wp-content/uploads/2010/12/Shrink11-300x119.jpg
Os dados das tabelas distribuidos no disco após o SHRINK

http://www.sqlinsiders.net/wp-content/uploads/2010/12/Shrink2-300x119.jpg
O espaço em branco no arquivo pode ser eliminado e devolvido ao sistema operacional.

Suponhamos que arquivo tem 100MB de tamanho e que após o SHRINK reduziu para 10MB, os 90MB em branco foram deveolvidos.

A princípio, o arquivo ficou mais organizado, mas se ela tivesse um índice NON-CLUSTERED, a referência às páginas estaria equivocada e o índice seria inutilizado para as queries imediatamente.

Mesmo para uma tabela com índice CLUSTERED teríamos problemas, pois um índice não atualizado após um Shrink automaticamente, sendo assim, sempre que movemos páginas pelo shrink, estamos fragmentando nossos índices.

Veja um exemplo:

Considere duas tabelas com índices CLUSTERED:

http://www.sqlinsiders.net/wp-content/uploads/2010/12/Shrink3-300x119.jpg

Por ter índice CLUSTERED, as tabelas tem as páginas próximas umas das outras.

Imagine um DROP na tabela representada pela cor AZUL. A estrutura do arquivo ficaria assim:
http://www.sqlinsiders.net/wp-content/uploads/2010/12/Shrink4-300x117.jpg

Após fazer um SHRINK, o SQL Server move as páginas usadas do final para o começo, sendo assim:

http://www.sqlinsiders.net/wp-content/uploads/2010/12/Shrink5-300x118.jpg

O que houve aqui, foi uma mudança em massa dos dados para o começo do arquivo. Como o índice não é atualizado após um SHRINK, suas referências estão totalmente inválidadas. Isso é chamado de FRAGMENTAÇÃO.

Se ainda assim você pretende usar o SHRINK, vai aí uma dica valiosa: Reconstrua os seus índices imediatamente após.

OBS: Não confunda fragmentação de arquivo do Windows com a fragmentação das Páginas de dados dos arquivos do SQL Server. Discutiremos isso em outra oportunidade.

Como funciona o SHRINK no arquivo de log de transações

Se no arquivo de dados o Shrink pode causar uma bagunça muito grande nos seus índices, no arquivo de log é super tranquilo.

Só para lembrar, um arquivo de log registra as transações de forma sequencial identificando-as através de LSNs (Log Sequence Number).

Por não trabalhar com páginas de dados, a redução do arquivo não altera em nada o funcionamento do SQL Server, você perde nada e ganha espaço no arquivo de log.

Aqui cabe uma explicação: O DBA que usa o artificio de rodar um SHRINK para reduzir o tamanho do seu arquivo de log, provavelmente está sendo descuidado em alguma etapa de suas tarefas de rotina. Vamos às possibilidades:

1) Se o arquivo de log cresce muito durante o dia e você precisa de rodar o Shrink a noite para reduzir o tamanho dele é porque o backup do log não é feito frequentemente. O backup log LIMPA o log evitando assim que ele tenha que usar o Autogrowth para esticar o tamanho dele. Se você faz backup do log de hora e hora, por exemplo, é pouco provável que precise de usar o Autogrowth. Lembre-se que backup FULL não limpa o log.

2) Se cresce tanto assim o arquivo de log e você não faz backup, mude o Recovery Model do seu banco para SIMPLE. Assim, o SQL Server vai truncar o log a cada checkpoint evitando acúmulo de transações registradas. É muito comum ver nas empresas banco de dados com 5 GB de dados e um log de 70 GB.

Fazendo o SHRINK

O Shrink pode ser feito no banco de dados inteiro ou em um arquivo específico. Isso vai depender da sua necessidade. De qualquer forma, isso pode ser feito via Management Studio e com comando DBCC.

Shrink no Banco de Dados (DBCC SHRINKDATABASE)

O Shrink no banco de dados é feito através do comando DBCC SHRINKDATABASE.

O DBCC SHRINKDATABASE recebe como parâmetro o nome do banco e o espaço que deseja ter livre nos arquivos após a execução.

Veja alguns exemplos de uso:

DBCC SHRINKDATABASE (NomeBanco, 10)

• – Reduz o tamanho dos arquivos do banco de dados deixando 10% de espeço livre neles.

DBCC SHRINKDATABASE(NomeBanco, NOTRUNCATE)

• – A opção NOTRUNCATE na realidade só move as páginas de dados do final do arquivo para o começo e não devolve o espaço liberado para o sistema operacional. A impressão que dá é que nada foi feito com os arquivos de dados. Só aplicavel à arquivo de dados.

DBCC SHRINKDATABASE(NomeBanco, TRUNCATEONLY)
– Libera todo espaço disponível no final do arquivo, mas não movimenta as páginas de dados. O arquivo de dados só é reduzido até o último extent alocado. Só aplicavel à arquivo de dados.

Se desejar, pode fazer pelo Management Studio clicando com botão direito no banco à Tasks à Shrink à Database e escolha a porcentagem.

http://www.sqlinsiders.net/wp-content/uploads/2010/12/Shrink6-300x154.jpg

Shrink nos Arquivos individualmente (DBCC SHRINKFILE)

O shrink em um arquivo individual é feito através do comando DBCC SHRINKFILE.

Exemplo:

Use Database1
go

DBCC SHRINKFILE (DataFile1, 7)

• – Reduz o tamanho do arquivo DataFile1 para 7 MB. Lembrando que se esse arquivo for de LOG, o tamanho desejado pode não ser respeitado, pois a estrutura de um arquivo de log não permite que seja reduzido valores menores do que o tamanho do Virtual Log.

Se usar o Management Studio, clique com botão direito no banco à Tasks à Shrink à File�
http://www.sqlinsiders.net/wp-content/uploads/2010/12/Shrink7-300x270.jpg

Release unused space à Libera todo espaço disponível no final do arquivo, mas não movimenta as páginas de dados.

Reorganize pages before releasing unused space à Movimenta as páginas para o começo do arquivo antes de liberar o espaço.

Empty file by migrating the data to other files in the same filegroup à Caso tenha definido vários arquivos de dados para o seu banco e tem a necessidade de eliminar um ou mais arquivos alocados, pode usar essa opção. Ela faz com que as páginas de dados do arquivo em questão sejam migradas para outras páginas dos outros arquivos do mesmo filegroup. Após feito isso, você poderá apagar o arquivo.

Dicas e Observações

1) Use a procedure sp_spaceused para saber o espaço em disco ainda não alocado, para ter uma base de redução do arquivo.

http://www.sqlinsiders.net/wp-content/uploads/2010/12/Shrink8-300x162.jpg

2) Também pode ser usado o cálculo a seguir:

SELECT name ,size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files

http://www.sqlinsiders.net/wp-content/uploads/2010/12/Shrink9-300x87.jpg

3) O banco de dados não pode se tornar menor que o tamanho original da criação do banco de dados ou do tamanho explicitamente definido por meio de um ALTER DATABASE ou DBCCSHRINKFILE.

4) Usuários podem continuar trabalhando com o banco de dados durante o SHRINK, o banco não precisa estar em SINGLE USER MODE.

5) O SHRINK não roda se algum backup estiver em andamento no banco de dados.

6) Os erros 5202 para SHRINKDATABASE e 5203 para SHRINKFILE podem ocorrer caso nível de isolamento do banco esteja como SNAPSHOT. Nesse caso, os erros são disparados a cada cinco minutos, na primeira hora, e, depois, a cada hora.

7) Você pode reduzir o tamanho padrão de um arquivo vazio usando DBCC SHRINKFILE target_size. Por exemplo, se você cria um arquivo com 5 MB e depois o reduz para 3 MB enquanto o arquivo ainda está vazio, o tamanho do arquivo padrão é definido como 3 MB. Isso só se aplica a arquivos vazios que nunca contiveram dados.