É seguro ter a redução automática do SQL Server ativada?

Question

Existem muitas opções do SQL Server que podem ser ativadas para bancos de dados, e uma das mais incompreendidas é a de encolher automaticamente. É seguro? Se não, por que não?

    

Answer 1

(Eu perguntei originalmente como uma pergunta regular, mas depois descobri o método correto - obrigado BrentO)

Não, nunca.

Eu me deparei com isso várias vezes agora no ServerFault e quero alcançar uma boa audiência com alguns bons conselhos. Se as pessoas desaprovam essa maneira de fazer as coisas, diminua o preço e eu removerei isso com prazer.

A redução automática é uma configuração de banco de dados muito comum para ativada. Parece uma boa ideia - remova o espaço extra do banco de dados. Há muitos 'DBAs involuntários' por aí (pense em TFS, SharePoint, BizTalk ou apenas SQL Server antigo) que podem não saber que a redução automática é positiva.

Enquanto estava na Microsoft, eu costumava ter o Mecanismo de Armazenamento do SQL Server e tentei remover o recurso de redução automática, mas ele precisou ficar por compatibilidade com versões anteriores.

Por que o encolhimento automático é tão ruim?

O banco de dados provavelmente crescerá novamente, então por que diminuí-lo?

1. Diminuir o crescimento e diminuir o tamanho gera fragmentação no nível do sistema de arquivos e consome muitos recursos.
2. Você não pode controlar quando começa (mesmo que seja normal)
3. Ele usa muitos recursos. A movimentação de páginas no banco de dados requer CPU, muito IO e gera muito log de transações.
4. Aqui está o kicker real: o tamanho do arquivo de dados (auto ou não) causa uma fragmentação massiva do índice, o que leva a um desempenho ruim.

Eu fiz um post no blog há um tempo atrás que tem um exemplo de script SQL que mostra os problemas que ele causa e explica com mais detalhes. Consulte Auto-shrink - desligue-o! (sem publicidade ou lixo como esse no meu blog). Não fique confuso com a redução do arquivo de log, o que é útil e necessário na ocasião.

Então, façam um favor a si mesmos - olhem nas configurações do banco de dados e desliguem automaticamente. Você também não deve reduzir seus planos de manutenção exatamente pelo mesmo motivo. Espalhe a palavra para seus colegas.

Editar: devo acrescentar isso, lembrado pela segunda resposta - há um equívoco comum de que interromper uma operação de redução pode causar danos. Não, não vai. Eu costumava possuir o código de redução no SQL Server - ele reverte a movimentação da página atual que está sendo feita se interrompida.

Espero que isso ajude!

    

Answer 2

Claro, Paul está certo.

Veja todos os bancos de dados e suas configurações de autoshrink. Se você tem muitos bancos de dados, um vai entrar.

sp_msforeachdb  @command1 = 'Select ''[?]'',DATABASEPROPERTYEX(''?'',''IsAutoShrink'')'

Isso está no dmv em algum lugar ... eu me pergunto.

    

Answer 3

Não é "inseguro" - não prejudica nada.

Mas não é recomendado para ambientes de produção em que o banco de dados pode decidir desligar e iniciar um dispendioso exercício de rearranjo pouco antes de uma pilha de solicitações chegar, fazendo com que essas solicitações demorem mais para serem atendidas. Você está muito melhor usando as operações de redução de agendamento, juntamente com outras operações de manutenção, como backups (na verdade, após os backups, o log de transações será maior). Ou simplesmente não encolher a menos que haja um problema de crescimento - você sempre pode configurar um monitor para informá-lo quando o espaço alocado não utilizado ultrapassar determinada proporção ou tamanho fixo.

IIRC a opção está desativada por padrão para todos os bancos de dados em todas as edições do MSSQL, exceto Express.

    

Answer 4

Há um white paper disponível no TechNet que explica a manutenção do SQL em mais detalhes.

link

    

Answer 5

Eu vi um servidor SQL com o Autogrow e o Autoshrink ativado. Este servidor (relativamente poderoso) era terrivelmente lento, porque tudo o que fazia o dia todo era diminuir e aumentar os arquivos do banco de dados. Autoshrink pode ser útil, mas eu recomendo duas coisas:

1. Desativar a desativação automática por padrão.
2. Documente as configurações do seu servidor para saber onde o Aumento Automático e o Autoshrink estão habilitados e onde eles não estão.

Answer 6

A única vez que fui forçado a reduzir um banco de dados foi atualizar uma cópia em um servidor de teste com menos espaço em disco (insuficiente para armazenar o banco de dados de produção).

O (s) arquivo (s) do banco de dados de produção tinha espaço livre generoso, mas infelizmente é necessário restaurar um banco de dados com o (s) mesmo (s) arquivo (s) de que você fez o backup. Então, não teve escolha senão encolher a produção antes de fazer o backup. (O psiquiatra levou muito tempo, muitos recursos foram consumidos e o crescimento subseqüente do log de transações foi problemático.)

    

Answer 7

Veja também este tutorial em vídeo ....

Assista a Paul Randal demonstrando como a redução e a redução automática podem causar sérios problemas de fragmentação em seu banco de dados link