Eu tenho um banco de dados MS SQL Server 2000 de 9 GB que tem cerca de 1 GB de dados reais nele. Está em um host compartilhado. Preciso reduzir o tamanho para evitar o pagamento de taxas extremas de hospedagem.
Eu tentei usar DBCC SHRINKDATABASE e DBCC SHRINKFILE . Ambos não reduzem o banco de dados. ( E sim, eu sei que eu não deveria realmente usar eles. )
DBCC SHRINKDATABASE (nome_bd) retorna:
DbId FIleId CurrentSize MinimumSize UsedPages EstimatedPages 23 1 1114808 128 1113824 1113824 23 2 63 63 56 56
DBCC SHRINKFILE (1) retorna:
DbId FIleId CurrentSize MinimumSize UsedPages EstimatedPages 23 1 1114808 128 1113824 1113824
Na página de propriedades gerais, o tamanho é listado como 8.709 MB. O espaço disponível está listado como 0 MB. Na página de propriedades dos arquivos, o arquivo principal é listado com um tamanho inicial de 8.652 MB.
DBCC SHOWCONTIG (bigTable) retorna:
- Pages Scanned................................: 807 - Extents Scanned..............................: 103 - Extent Switches..............................: 102 - Avg. Pages per Extent........................: 7.8 - Scan Density [Best Count:Actual Count].......: 98.06% [101:103] - Logical Scan Fragmentation ..................: 0.37% - Extent Scan Fragmentation ...................: 1.94% - Avg. Bytes Free per Page.....................: 68.5 - Avg. Page Density (full).....................: 99.15%
Rodando sp_spaceused , eu recebo isto:
databaseize: 9131.94 MB
espaço não alocado: 377,95 MB
Em execução sp_spaceusused bigTable , obtenho isto:
Linhas: 56,095
Reservado: 6,419,736 KB
Dados: 252,656 KB
Index_size: 4,640 KB
Não utilizado: 6,162,440 KB
Rodando sp_spaceusused bigTable2 , eu recebo isto:
Filas: 2,791
Reservado: 2,362,744 KB
Dados: 114,232 KB
Index_size: 200 KB
Não utilizado: 2,248,312 KB
Todas as outras tabelas são pequenas.
Pedi ao meu host para fazer backup do banco de dados, descartar e recriá-lo com um tamanho inicial mais baixo e restaurá-lo. Eles tentaram isso, e essa foi a resposta deles: "Nós seguimos as etapas, mas isso não corrigiu o problema, mas o SQL Server aumentou o tamanho do banco de dados e seu tamanho atual é superior a 9 GB." (Foi 7GB antes que eles tentaram isso.)
É possível que meu banco de dados precise dos 8 GB de espaço não utilizado? Assumindo que isso não acontece, que outras opções eu tenho para encolher?
Consegui fazer o download de uma cópia do meu host para trabalhar no meu computador local. Eu instalei a versão de avaliação do SQL Server 2008 e anexei o banco de dados. Eu corri ALTER INDEX ALL ALL NO BIGTABLE REORGANIZE, ALTER INDEX ALL NO BIGTABLE REBUILD, e DBCC SHRINKDATABASE. Nada mudou. Ainda estou recebendo as mesmas respostas básicas dos vários comandos listados acima.
Qual é o modo de recuperação deste banco de dados? Antes de executar o DBCC_SHRINKDATABASE, você deve fazer backup de seus logs de transação. Eu estou supondo que o modo de recuperação está cheio? O que provavelmente é melhor na maioria dos casos.
Qual a frequência dos seus backups de banco de dados para este banco de dados? e quando foi o último backup de log de transação de execução.
você também pode tentar DBCC SHRINKDATABASE (DatabaseName, TRUNCATEONLY);
Quando você fez eles soltarem o banco de dados, recriá-lo em um tamanho menor e restaurar o banco de dados, nada deveria ter mudado. Quando você restaura um banco de dados, a primeira coisa que o SQL Server faz é eliminar o banco de dados que está lá.
O uso do comando DBCC SHRINKFILE é o método correto a ser executado. Você só precisa obter o SQL para permitir que você mova alguns dados.
Pode ser necessário reconstruir os índices nas tabelas para compactar as páginas de dados para permitir que o SQL Server remova parte do espaço livre das tabelas. Quando você usa o DBCC SHRINKFILE para reduzir o arquivo físico, ele se moverá pelas páginas de dados dentro do arquivo, de modo que todas as páginas vazias estejam no final do arquivo, então ele poderá liberar as páginas de dados. No entanto, se suas páginas estiverem parcialmente cheias, o SQL Server não as combinará para preenchê-las mais quando usar esse comando.
Isso pode ser feito usando os comandos reindex (os comandos reais variam dependendo da versão do SQL Server em uso). Isto irá compactar os registros alguns (dependendo do nível do fator de preenchimento especificado quando a tabela / índices foram criados.
Você também deve saber que o SQL Server 2000 e anteriores tiveram problemas ao lidar com tipos de dados text e ntext e mover dados dentro das páginas de dados. Essas versões mais antigas do SQL Server deixariam fragmentos de dados contidos nos arquivos de dados que você não conseguiria limpar, já que não estão associados a nenhuma linha.
Seus problemas provavelmente relacionados ao texto excluído ou dados ntext não estão liberando o espaço excluído - isso é o mesmo em todas as versões da página.
A sugestão de índices de reconstrução era falsa - o SHOWCONTIG claramente e sp_spaceused mostram claramente que o espaço desperdiçado não está nos índices. O backup da sugestão de log também era falso - o sp_spaceused mostra que o espaço extra está nos arquivos de dados, não no log.
Você tem colunas text / ntext na tabela? Nesse caso, a única maneira de recuperar esse espaço em 2000 e antes é criar uma nova tabela e, em seguida, exportar / importar os dados para ela. Somente em 2005 eu adicionei a compactação de LOB para reduzir e ALTER INDEX ... REORGANIZE.
Além disso, lembre-se de que houve dois erros no encolhimento que realmente aumentariam a quantidade de espaço ocupado pelos dados text / ntext - verifique se você está no SP4.
Obrigado
Como um pensamento, você pode fazer um backup e baixá-lo localmente, atualizá-lo para o SQL 2008 e, em seguida, tentar reduzi-lo em sua própria máquina? Se funcionar, envie-a de volta. Solte o atual e substitua-o pelo recém-encolhido. Isso também significa que você precisa atualizar sua conta para o SQL 2008, mas pelo menos pode provar localmente se funcionará.
David,
Por favor, você poderia postar as definições das grandes tabelas?
<ignore ref="see comments" editor="self" > Um problema comum que resulta em tabelas muito grandes e não limpáveis é a escolha inapropriada de tipos de dados ou estruturas de dados. As tabelas podem estar usando tipos de dados de tamanho fixo para dados de tamanho variável. Por exemplo, se você tiver um campo definido como char (100) e muitas entradas forem menores que os 100 caracteres (ou mesmo NULL, nesse caso), a tabela ainda exigirá todos os 10 caracteres de armazenamento. Nesse caso, uma escolha melhor seria varchar (100), que armazenará apenas os dados e não preencherá os dados desnecessariamente. </ignore >
Algumas outras possibilidades são que seu provedor de hospedagem pode estar usando os qualificadores TRUNCATEONLY ou NOTRUNCATE (e, portanto, não permitindo que os dados sejam movidos para páginas não alocadas, perdendo completamente o ponto de sua solicitação) ou eles podem estar especificando a porcentagem de destino incorretamente (a porcentagem de destino é a quantidade de espaço livre em relação aos dados armazenados que você deseja após a operação de redução). Em ambos os casos, você poderia simplesmente pedir os comandos SQL que eles estão emitindo ou ver uma captura de tela da caixa de diálogo antes de emitir a diretiva de encolhimento, se eles estiverem usando a GUI para reduzir o banco de dados.
Espero que isso ajude.
Jessica
Tags sql-server