Mitigar os efeitos negativos do encolhimento

Aqui está minha abordagem um tanto manual para encolher arquivos. Isso funcionou muito bem para mim por mais de dez anos.

Encolher arquivos não é necessariamente ruim, mas isso não significa que seja sempre a melhor coisa a fazer.

Em primeiro lugar, pense por que você está encolhendo. A maioria dos bancos de dados só crescerá. Se você espera precisar do espaço em um futuro próximo, provavelmente não quer encolher.

A melhor razão para encolher é que você está tentando se recuperar de algum erro, onde o tamanho do arquivo de dados cresceu muito além de qualquer coisa que seja necessária em um futuro próximo.

Outro bom motivo é que você precisa restaurar o banco de dados afetado em servidores de desenvolvimento ou teste que simplesmente não têm capacidade de armazenamento para lidar com o espaço extra não utilizado.

O pior motivo para encolher é que você está tentando colocar outro banco de dados no armazenamento que já está quase cheio. Você vai ficar sem espaço, aceitá-lo é o primeiro passo. A próxima etapa é trabalhar em um plano para mais armazenamento (ou menos bancos de dados), não roubar Peter para pagar Paul.

Encolher tempdbé quase sempre doloroso. É difícil obter reduções oportunas sem reiniciar a instância e reiniciar a instância é um mau hábito.

Em segundo lugar, certifique-se de deixar espaço extra nos arquivos MDF e NDF . A reindexação precisará de algum espaço de trabalho. Quantos? Depende. Encontre o tamanho da sua maior mesa e use-a como guia. Nunca errei deixando tanto espaço nos arquivos de dados. Se você não tiver espaço suficiente em seus arquivos, eles tentarão crescer automaticamente. Se o SQL Server não tiver espaço contíguo no arquivo, você poderá ter problemas ao reindexar tabelas grandes; eles nunca parecerão ter a propriedade desfragmentada após a execução das rotinas de reindexação.

Não use encolhimento automático. Ele foi planejado para bancos de dados executados nas estações de trabalho dos usuários, não em servidores de produção dedicados e foi pensado há tanto tempo que usá-lo por qualquer motivo não é uma boa ideia neste momento.

Use dbcc shrinkfile, não dbcc shrinkdatabase. Se você precisar encolher um banco de dados com vários * MDF *s, faça isso de forma round-robin, prestando atenção aos grupos de arquivos aos quais cada arquivo pertence. Para distribuir a E/S com eficiência, o SQL Server deseja arquivos de tamanho igual em um grupo de arquivos.

Geralmente, Shrinkfile não coloca muita carga no servidor, mas quando reduzo arquivos, evito períodos de pico de demanda e gosto de observar um pouco mais de perto o Performance Monitor do que faria normalmente.

Não tente reduzir todo o espaço de uma só vez. Se você tentar reduzir uma grande quantidade de espaço de uma só vez, o comando dbcc frequentemente parecerá catatônico. A E/S de disco será descartada, mas o comando não será concluído.

Depois de executar o dbcc shrinkfile, você desejará reindexar para colocar os dados de volta em ordem. Dependendo de suas tabelas, você poderá fazer a reindexação online, que é amplamente documentada.

Se você encolheu demais os arquivos, eles tentarão crescer automaticamente. Você não quer isso, então tome cuidado para não encolher muito as coisas. em caso de dúvida, deixe um GB extra.

A fragmentação em nível de arquivo tende a ocorrer mais em volumes que possuem muitos arquivos de dados que geralmente são aumentados. A eliminação/recriação frequente de bancos de dados pode agravar o problema. Se você estiver reduzindo os arquivos, eles devem ficar menores e deve haver menos fragmentos. Não há nenhum motivo específico para que a desfragmentação piore as coisas, embora isso coloque carga de E/S em seu servidor, o que pode prejudicar se o armazenamento já estiver muito carregado. Alguns administradores confiam em ferramentas de desfragmentação de terceiros, mas só usei a ferramenta da Microsoft em servidores. Novamente, eu não faria isso durante os horários de pico de demanda.