none
diminuição de banco de dados RRS feed

  • Pergunta

  • Olá a todos.

    Sou novato no mundo sql server. 

    (...)

    Estou querendo apagar vários registros de um banco de dados de teste para diminuir o tamanho dele.  Percebi que depois de excluir milhões de linhas da maior tabela do banco, o arquivo de log praticamente dobrou o tamanho. Não deletei nem 20% dos registros ainda. 

    A minha pergunta é: como posso diminuir o log? É possível?  

    segunda-feira, 13 de agosto de 2012 15:01

Respostas

Todas as Respostas

  • Boa tarde,

    Você pode usar o comando dbcc shrinkfile(arquivo_log, 2000) , no caso 2000 vai ser 2gb de arquivo.

    []'s

    segunda-feira, 13 de agosto de 2012 15:09
  • Rico,

    É possível sim, mas veja bem, execute esse procedimento apenas no seu banco de testes, jamais em bancos de produção.

    Se estiver no SQL2005:

    -- FORÇAR CHECKPOINT
    CHECKPOINT
     
    -- LIMPAR O TRANSACTION LOG
    BACKUP LOG <nome_database, sysname, > WITH TRUNCATE_ONLY
     
    -- DIMINUIR O TAMAMHO FISICO DOS ARQUIVOS
    DBCC SHRINKFILE (<nome_database, sysname, >, 1)
    DBCC SHRINKFILE (<nome_database, sysname, >_Log, 1)

    Se estiver no 2008 ou superior:

    -- FORÇAR CHECKPOINT
    CHECKPOINT
     
    -- -- DIMINUIR O TAMAMHO FISICO DOS ARQUIVOS
    GO
    ALTER DATABASE <nome_database, sysname, > SET RECOVERY SIMPLE
    GO
    DBCC SHRINKFILE (<nome_database, sysname, >, 1)
    DBCC SHRINKFILE (<nome_database, sysname, >_Log, 1)
    GO
    ALTER DATABASE <nome_database, sysname, > SET RECOVERY FULL
    GO

    Estes comandos vão reduzir seus arquivos para 1Mb, porém, caso os arquivos contenham dados, eles permanecerão intactos.

    Espero ter ajudado.

    []'s

    segunda-feira, 13 de agosto de 2012 18:46
  • Muito obrigado pela dica!

    Rico CS

    terça-feira, 14 de agosto de 2012 14:24
  • Bom dia Rico.cs,

    altera seu recovery model para "SIMPLE" e aplique os comandos passados pelo Logan. Se é ambiente de desenvolvimento sem problemas, mas para ambiente de "Produção", é bom você dar uma lida no artigo do MVP Gustavo Maia sobre essa questão.

    Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte I
    http://gustavomaiaaguiar.wordpress.com/2009/08/01/piores-praticas-%e2%80%93-utilizar-o-comando-backup-log-com-a-opcao-with-truncate_only-%e2%80%93-parte-i/

    Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte II
    http://gustavomaiaaguiar.wordpress.com/2009/08/01/piores-praticas-%E2%80%93-utilizar-o-comando-backup-log-com-a-opcao-with-truncate_only-%E2%80%93-parte-ii/

    terça-feira, 14 de agosto de 2012 14:34
  • Andre,

    Recomendo seguir esta sequência de passos:

    ALTER DATABASE 'NomeBancodeDados'  
      SET RECOVERY SIMPLE;
    
      DBCC ShrinkDatabase('NomeBancodeDados',10)
    
      DBCC ShrinkFile(1,TruncateOnly);
    
      DBCC ShrinkFile(2,100);
      
      DBCC ShrinkFile(2,TruncateOnly);
    
      ALTER DATABASE 'NomeBancodeDados' 
      SET RECOVERY FULL;
    Vale ressaltar que o comando Backup Log não funciona mais no SQL Server 2008.

    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    terça-feira, 14 de agosto de 2012 17:54
    Moderador
  • Boa Noite,

    Me espanta o quanto soluções pontuais e incorretas são postadas e tidas como certas quanto falamos de arquivos de log que crescem. Não é a primeira vez que vejo soluções de truncar o log como respostas aos problemas (ainda que os autores possivelmente desconheçam os efeitos colaterais). O que impressiona é que esse pensamento completamente equivocado não é apenas compartilhado e reproduzido por muitos como também está presente em grandes softwares house no mercado brasileiro (vide o ERP de duas letras).

    Primeiramente, o comando BACKUP LOG não foi descontinuado no SQL Server 2008. O que foi descontinuado, foi a opção WITH TRUNCATE_ONLY e não o comando BACKUP LOG. O comando BACKUP LOG é imprescindível para efetuar backups e não está descontinuado.

    Outro ponto é recomendar a opção de trocar o Recovery Model para SIMPLE e depois mudá-lo para FULL. Se analisarmos um pouco como funciona o arquivamento das transações no log saberemos que essa sequência de passos não tem o menor sentido.

    O Recovery Model Full diz que as transações serão arquivadas no log e só serão descarregas quando você efetuar um backup. No momento em que você descarrega as transações do log para um arquivo de backup, você poderá utilizá-la no futuro para restaurar um banco de dados. É possível por exemplo restaurar um backup full e aplicar as transações desse backup pra chegar a um ponto específico. O ponto positivo é a capacidade de voltar a base em momentos específicos e o ponto negativo é que você precisa efetuar o backup de log ou o log irá encher até comprometer ou estourar o espaço (o que parece ter acontecido). Embora você tenha excluído vários registros da base, o log de transações precisa registrar isso tudo, pois, você pode querer voltar a base em um momento após os DELETEs (afinal foi só 20%) e por isso ele encheu (ainda que hajam poucos dados).

    O Recovery Model Simple diz que as transações serão arquivadas no log, mas a partir do momento em que o processo de checkpoint tira essas entradas do log e as efetiva no banco de dados, as mesmas são descartadas em definitivo do log. Isso significa que o log está sempre sendo reciclado e jamais irá encher. O ponto positivo é que você não esquenta a cabeça em ficar backupeando o log para ele não estourar, mas o ponto negativo é que você perde a possibilidade de voltar a base em um momento específico. Você terá a sua disposição apenas a chance de voltar a base nos momentos em que houver um backup full disponível ou no máximo um diferencial. Se você tem um backup FULL de segunda e um FULL de quinta, ou você volta segunda ou volta quinta. Não dá pra voltar na terça ou na quarta por exemplo (o que seria possível no Recovery Model Full com backups de log).

    No momento em que você muda o recovery model para simple, você está abrindo mão do log e inutilizando-o e é justamente por isso que ele diminui, pois, as transações são retiradas do log sem a cópia para um arquivo de backup. Pode parecer uma boa coisa, mas você irá abrir mão de uma possibilidade interessante no restore. Isso até está correto, pois, você pode querer mudar o Recovery Model para Simple simplesmente porque não está interessado em ficar backupeando log e não quer as vantagens do Recovery Model Full.

    O que definitivamente não faz sentido é mudar o recovery model para simple, perder o log e depois mudar para full. Isso vai fazer com que o log seja truncado e inutilizado e quando você mudar para FULL ele voltará a encher. Só que como ele foi truncado, o seu uso fica inviável até o próximo backup full, o que em outras palavras faz com que você abra mão do benefício e tenha de ficar administrando o crescimento a troco de nada.

    Se você não quer o log para voltar backups, simplesmente mude o recovery model para simple, faça o SHRINK e não volte o recovery model para full. Nunca mais você terá que reduzir o log se fizer isso (mas nunca mais irá poder contar com ele para restaurar alguma coisa). Se você quer o log para voltar backups, simplesmente faça backups de log para evitar que ele encha e guarde esses backups para utilizá-los posteriormente.

    Agora em hipótese nenhuma mude o recovery model para SIMPLE e depois para FULL. Você só estará perdendo o seu tempo em uma tarefa repetitiva que não serve para nada.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    quarta-feira, 15 de agosto de 2012 03:47
  • ALTER DATABASE <nome_database, sysname, > SET RECOVERY FULL GO


    Não tinha visto isto.

    View Ricardo Muramatsu's profile on LinkedIn

    quarta-feira, 15 de agosto de 2012 10:44
  • Gustavo,

    Muito bom o teu texto. Lendo ele, notei que poderia parar para explicar melhor algumas situações e tentar entender melhor a situação do Rico para só então dar a alternativa. Achei melhor já ir direto ao ponto passando os procedimento que eu utilizava quando necessário (talvez pelo pouco tempo no forum.

    Eu tinha para mim que a alteração para o modo SIMPLE era obrigatório para realizar o shrinkfile, uma vez que o próprio BOL cita isso no exemplo B (http://msdn.microsoft.com/pt-br/library/ms189493(v=sql.105).aspx), o que realmente não me parece ser necessário.

    Tenho esse script que eu passei para aplicar nos bancos de desenvolvimento, uma vez que volta e meia pegamos bancos de dados de clientes com logs muito grandes (justamente por falta de uma política de backup correta) e precisamos diminuir o tamanho dos logs para evitar uma alocação de espaço desnecessário, mas com essa sua informação, realmente, não tem porque (no meu caso), mantermos o modo de recovery em full - até então não tinha me atentado para isso, talvez por ser algo relativamente temporário e que não impacte diretamente no processo.

    Já nos bancos de testes, quando precisamos, voltamos para full e aplicamos a política interna para posterior restauração.

    Grande abraço e peço desculpas por alguma má interpretação que eu possa ter cometido na resposta anterior. :-)

    quarta-feira, 15 de agosto de 2012 11:07
  • Boa Tarde Logan,

    Tenho visto que você vem participando ativamente dos fóruns de SQL Server e por favor continue assim. A participação ativa no fórum aumenta significativamente a qualidade das respostas e discussões que se apresentam.

    De fato, para efetuar um SHRINK não é preciso alterar o Recovery Model e o próprio link mostra alguns exemplos sem esse pré-requisito. Entretanto, após visualizar o link que você postou, vi que fica subentendida a recomendação de truncar o recovery model para SIMPLE, efetuar o SHRINK e mudar o recovery model para FULL. Obrigado por reportar esse detalhe. Irei submeter diretamente ao time responsável para que possa ser feita uma avaliação sobre a permanência desse item em uma futura atualização. Considerando que o Books OnLine é uma excelente referência, pode estar aí uma das causas dessa orientação.

    Eu agradeço desde já a sua participação e a referência.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    quarta-feira, 15 de agosto de 2012 15:39
  • Gustavo, muito obrigado pela sua resposta. É exatamente o que eu queria saber. Como o banco é apenas para teste, não tenho interesse em recuperá-lo depois. Vou alterar o modo para simple. 

    (...)

    Já faz algum tempo que acompanho o seu blog. Muito bom, por sinal. Inclusive me inscrevi no newsletter dele. 

    Valeu a todos!!


    Rico CS

    sexta-feira, 17 de agosto de 2012 16:39
  • Pessoal, tenho mais uma pergunta que é continuação da primeira.

    Desculpe se posso estar fazendo uma pergunta óbvia...

    Alterei o modo recovery para simple. deletei os registros que queria mas o tamanho do banco continua o mesmo. Quando restaurei o back up que fiz ele ficou do mesmo tamanho. O que devo fazer agora?


    Rico CS

    segunda-feira, 3 de setembro de 2012 17:37