Eu sei que o psiquiatra é o diabo: ele inverte a ordem das páginas e é responsável pelo câncer de pele, fragmentação de dados e aquecimento global. A lista continua... Dito isto, digamos que eu tenha um banco de dados de 100 GB e exclua 50 GB de dados -- não em uma tabela, mas uma poda geral de dados antigos em um nível de banco de dados amplo, cobrindo 90% do tabelas - isso constitui um caso de uso apropriado para reduzir o banco de dados?
Se não, quais são as etapas apropriadas para limpar a casa depois de remover uma porcentagem tão alta de dados de um banco de dados? Posso pensar em dois: Reconstruir Índices e Atualizar Estatísticas. O que mais?
O banco de dados vai crescer novamente? Nesse caso, o esforço que você vai colocar nas operações de redução será um desperdício, porque quando você reduz o tamanho do arquivo e adiciona mais dados, o arquivo terá que crescer novamente e as transações precisam esperar que esse crescimento aconteça. Se você tiver configurações de crescimento automático abaixo do ideal e/ou uma unidade lenta, essa atividade de crescimento será bastante dolorosa.
Se você reduzir o banco de dados, para que usará o espaço em disco liberado? Novamente, se você vai apenas manter esse espaço livre caso esse banco de dados cresça novamente, então você está apenas girando suas rodas.
O que você pode considerar fazer, agora que você tem todo esse espaço livre no arquivo, é reconstruir seus índices para que eles sejam melhor otimizados (e será muito menos doloroso fazer isso quando você tiver espaço livre para isso - pense em tentar trocar um suéter em um armário minúsculo versus um quarto grande).
Portanto, a menos que esta seja uma grande operação de limpeza e você realmente não esteja subindo para o mesmo nível de dados novamente, eu deixaria como está e me concentraria em outras áreas de otimização.
Uma reorganização e redução nunca é realmente recomendada.
Se você puder colocar os aplicativos que o banco de dados está atendendo offline, poderá acelerar o processo e reduzir a fragmentação do índice removendo todos os índices e restrições de chave primária/estrangeira antes da redução (isso significa que há menos dados a serem movidos, pois apenas o as páginas de dados serão embaralhadas e não as páginas de índice agora inexistentes, acelerando o processo) e recrie todos os índices e chaves.
Recriar os índices após a redução significa que eles não devem ser fragmentados significativamente, e retirá-los durante a redução significa que reconstruí-los não deixará muitos pequenos "buracos" na alocação de página nos arquivos que podem convidar à fragmentação posteriormente.
Outra opção se você puder colocar os aplicativos offline é migrar todos os dados para um banco de dados novo da mesma estrutura. Se o seu processo de compilação for sólido, você poderá criar esse banco de dados em branco rapidamente, se não criar um a partir do banco de dados atual (restaure um backup do atual, trunque/exclua todo o conteúdo das tabelas e execute uma redução completa).
Você ainda pode querer descartar todos os índices no destino e recriá-los posteriormente, pois isso pode ser muito mais eficiente ao alterar muitos dados indexados (100% neste caso). Para acelerar o processo de cópia, tenha o(s) arquivo(s) de dados do banco de dados de destino em unidades físicas diferentes da fonte (a menos que você esteja usando SSDs, nesse caso você não precisa se preocupar em reduzir os movimentos da cabeça), você pode movê-los para o local de origem quando terminar.
Além disso, se estiver criando o destino como novo (em vez de apagar uma cópia da origem), crie-o com um tamanho inicial que conterá todos os dados atuais mais alguns meses de crescimento - isso tornará a cópia de dados um pouco mais rápida novamente, pois ele não alocará novos espaços de vez em quando ao longo do processo.
Isso pode ser melhor do que usar a redução porque migrar os dados para um banco de dados novo replica a ação pretendida da operação de redução, mas potencialmente com muito menos fragmentação (que é a consequência não intencional de uma reorganização e redução). Um encolhimento simplesmente pega blocos do final do arquivo e os coloca no primeiro espaço mais próximo do início, sem fazer nenhum esforço para manter os dados relacionados juntos.
Suspeito que o resultado também será mais eficiente em termos de espaço, pois é provável que haja menos páginas usadas posteriormente. Um encolhimento apenas moverá as páginas parcialmente usadas, movendo os dados é mais provável que resulte em páginas inteiras, especialmente se você inserir no destino na ordem da chave/índice clusterizado de uma tabela (onde uma tabela tem um) e criar outros índices após a migração de todos os dados.
É claro que se você não puder colocar os aplicativos offline, apenas realizar um encolhimento é sua única opção; portanto, se você realmente precisar recuperar o espaço, faça isso. Dependendo de seus dados, padrões de acesso, tamanho do conjunto de trabalho comum, quanta RAM o servidor possui e assim por diante, a fragmentação interna extra pode não ser tão significativa no final.
Para a operação de cópia, o SSIS ou o T-SQL básico também funcionariam (a opção SSIS pode ser menos eficiente, mas potencialmente mais fácil de manter posteriormente). Se você criar os relacionamentos FK no final junto com os índices, poderá fazer um simples "para cada tabela, copiar" em ambos os casos. É claro que, para um caso único, um psiquiatra + reorganizar provavelmente também é bom, mas eu gosto de assustar as pessoas para nunca considerarem os psiquiatras regulares! (Conheço pessoas que os agendam diariamente).
Se você está ficando sem espaço e seus dados não devem ficar tão grandes, então encolha, mas reconstrua seus índices depois com fatores de preenchimento apropriados que permitem o crescimento típico.
Se o seu objetivo final for realmente reduzir o tamanho do backup, certifique-se de implementar uma estratégia de backup abrangente para limpar o log de transações e, ao fazer backup do banco de dados, use as opções de compactação.
Eu não recomendaria o crescimento automático de 5 GB, a menos que você normalmente espere crescer 5 GB com frequência. Caso contrário, você pode ter problemas de desempenho intermitentes. Seu tamanho de dados deve primeiro ser definido para o que você acha que é necessário para, digamos, um ano, e o Crescimento Automático deve ser definido para um tamanho que você testou não afeta o desempenho operacional. Consulte Não toque nesse botão de redução de banco de dados no SQL Server! por Mike Walsh.
A reconstrução de índices antes da redução faz com que os índices sejam mal dispostos. Não é bom reconstruir e depois encolher. A redução faz com que os índices sejam mutilados para recuperar espaço - portanto, reconstruir antes e depois diminuir é inútil. Consulte Quando usar o Encolhimento Automático de Thomas LaRock.
Voltando a esta MANEIRA tarde. Ainda assim, estamos ponderando e testando o uso da redução em nossos ambientes de teste há muito tempo. De acordo com o tópico, há momentos em que o encolhimento é uma opção viável. Mas saber quando e como aplicá-lo, são vitais para uma boa execução tanto a longo quanto a curto prazo.
Em nosso cenário, adicionamos recentemente várias alterações ao nosso grande banco de dados, incluindo compactação, particionamento, arquivamento e exclusão simples de dados redundantes. Como resultado, a parte usada de nosso arquivo de dados primário caiu para menos da metade do que costumava ser. Mas qual é o sentido de carregar toda essa bagagem? Especialmente porque, ao contrário de alguns artigos na web, o tamanho de seus arquivos de dados CORRELA-SE DIRETAMENTE COM A DURAÇÃO DO BACKUP / RESTORE. Isso porque, ao contrário de muitos artigos, cenários da vida real carregam mais dados em qualquer página do que apenas as coisas que você removeu.
Mais ao ponto, isso abre um ótimo cenário para o encolhimento:
Desta forma, os únicos dados deixados lá seriam os objetos do sistema do seu banco de dados, estatísticas, procedimentos e outros enfeites. A redução deve ser muito, MUITO mais rápida, e não há necessidade de nenhuma manutenção adicional de índice em seus principais objetos de dados que serão criados ordenadamente e com risco mínimo de fragmentação futura.
Não sei se isso funcionaria melhor do que reindexar após a redução, mas outra opção seria criar um novo arquivo de dados com tamanho adequado e mover todos os dados para ele. Nesse caso, eu faria uma reindexação primeiro para que você saiba qual é o tamanho real dos dados. Um problema é que, se este for o primeiro arquivo no arquivo de dados primário, não acho que você possa esvaziá-lo. Você deve ser capaz de reduzi-lo e depois mover os dados de volta e isso evitaria a reversão da página. No entanto, se você estiver pensando em mudar para o estado sólido, isso não deve fazer uma grande diferença de qualquer maneira.