none
SHRINKFILE LOGFILE RRS feed

  • Pergunta

  • Bom-dia Pessoal,

            Estou tentando reduzir o tamanho do meu arquivo de log do SQL Server 2012 e liberar espaço para o Sistema Operacional.

            Meu arquivo físico está com 7GB. Quero reduzi-lo para 1GB, então executei os seguintes comandos sendo que antes fiz um backup do logfile truncando o mesmo e depois executei um backup full por segurança.

            Abaixo vai os comandos que executei para reduzir o logfile e liberar espaço para o Sistema Operacional:

    1º Comando

    USE [LanView]
    GO
    DBCC SHRINKFILE (N'LanView_log' , 0, TRUNCATEONLY)
    GO

    2º Comando

    USE [LanView]
    GO
    DBCC SHRINKFILE (N'LanView_log' , 1024)
    GO

          O problema é que meu arquivo de log continua ocupando os mesmos 7GB físicos sem liberar nada para o Sistema Operacional.

          Neste caso qual a solução para este problema e porque isso acontece?

    Obrigado,

    Jurandyr F. Alves

    segunda-feira, 29 de dezembro de 2014 12:52

Respostas

  • Jurandyr,

           Entendo seu ponto de vista, espaço desperdiçado é custo. Entretanto, discordo que seja um problema. Problema é a falta de espaço na base. E também é custo. E talvez o custo seja maior que o espaço (teoricamente) desperdiçado. Vamos imaginar uma situação hipotética. Se você mantiver o seu TL em 4GB e consumir todo o espaço com outras aplicações, no momento que o seu TL precisar crescer para 7GB (e ele vai precisar, pode ter certeza disso, você já viu que ele precisou agora) você não terá os 3Gb disponíveis para ele crescer. Não é você que determina se precisa de 4Gb ou 7Gb ou mais é o espaço disponível no TL que exige que ele cresça mais ou menos. O SQL então utilizará todos os espaços disponíveis na base de dados e não permitirá mais nenhum insert/update/delete na base. Uma das formas de você limitar o tamanho do seu TL é limitar o crescimento ao tamanho máximo desejado, no seu caso, 4Gb, porém, novamente, se o SQL precisar de mais espaço e não estiver disponível, ele não permitirá mais nenhum insert/update/delete na sua base. Qual o custo da sua base ficar parada por 1h ou mais tempo? Notou que isso é o problema REAL?

           Por este motivo, Eu costumo dizer que o TL tem que ter o tamanho suficiente para ele, seja 1Gb, 10Gb ou 50Gb... 

           Pois bem, respondendo as suas perguntas (vou invertê-las para melhor explicar):

           2-Porque o TL cresce? 

               Para que o SQL possa atender as regras ACID (Atomicidade, Consistencia, Isolação, Durabilidade) a cada comando de INSERT, UPDATE, DELETE ele registra esse comando no TL (arquivo .LDF) exatamente na ordem que eles aconteceram e também na memória mas antes que essa informação seja refletida nas tabelas da base de dados (arquivo .MDF) . Isso significa que o dado que está na tabela é diferente do que realmente está na memória?? Sim. É exatamente isso! Mas vamos deixar esse conceito de Clean/Dirty Page para outro momento...  Creio que neste ponto você já entendeu porque ele cresce. Só que as coisas não são tão fáceis assim...

                O SQL trabalha internamente no TL com um conceito chamado VLF (Virtual Log File). Isso singnifica que o seu TL possui internamente dezenas (e as vezes milhares) de VLFs... O shrink só libera espaço correspondente a um VLF quando o VLF está liberado, ou seja, todas as transações já foram registradas nas devidas tabelas (lembra que no princípio os dados das tabelas estava diferentes do TL??) E isso acontece em diversos momentos, mas, normalmente depois de um CHECKPOINT. Imaginando que a sua base esteja em Recovery Model Simple, quando acontece o CHECKPOINT o SQL libera o espaço utilizado pela transação para ser reutilizado por outra transação e com isso reutiliza quase sempre poucos VLFs. Isso corresponde ao seu TL quase sempre ficar muito pequeno, normalmente com o tamanho original da criação do TL (1MB). Caso a sua base esteja em Recovery Model Full, o Checkpoint libera o VLF, mas mantem o espaço utilizado (aquilo que você chamou de despedício, mas o correto é espaço disponível no TL) no VLF até que haja a garantia que o dado estará preservado (Regra ACID de Durabilidade). E neste caso, ele só estará preservado quando for feito um backup. O Backup que libera o espaço utilizado no VLF é o backup do Transaction Log.

         1-Qual a solução para este problema?

              Conforme expliquei acima, esse comportamento do SQL Server não é um problema, mas é uma feature, e uma das mais importantes, que garante que os dados da sua base não serão perdidos. Ou seja, a sua pergunta é: Qual a solução correta para esse comportamento do SQL? Simples! Mantenha uma boa estratégia de Backup! Se a sua base estiver em Recovery Model simple, as opções são o backup Full e Differential, e se estiver como Recovery Model Full, você além de ter o backup Full e Differential, ainda (obrigatoriamente) terá que ter backup do Transaction Log.

        Espero ter ajudado!


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008





    segunda-feira, 29 de dezembro de 2014 17:43
    Moderador

Todas as Respostas

  • Jurandyr,

          Fazer o seu arquivo de log reduzir é fácil. Entretanto, diminuir o tamanho do seu arquivo de log é um procedimento que deve ser feito tendo conhecimento do que você deseja fazer e o porquê fazer. Porque você quer reduzir o tamanho do log? O seu ambiente é homologação?

           


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008

    segunda-feira, 29 de dezembro de 2014 13:59
    Moderador
  • Jurandyr,

    Concordo com o Roberto, e um outro ponto importante, analisando o seu código, você esta tentando fazer o TruncateOnly e depois fazer a redução do arquivo, provavelmente isso esta impactando na liberação do espaço.

    O correto seria fazer o encolhimento e depois o Truncate, veja este exemplo:

    Use SeuBanco Go

    Alter Database SeuBanco
    Set Recovery Simple;
    Go

    DBCC ShrinkFile(2,100); Go

    DBCC ShrinkFile(2,TruncateOnly);}
    Go

    Alter Database SeuBanco
    Set Recovery Full;
    Go

    Vale ressaltar que você terá que mudar o modelo de recuperação do Banco de Dados para Simples, afim de evitar possíveis problemas de perda de dados, pois todo processo de Shrink é passível de ocasionar perdas de dados.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | SoroCódigos] @JuniorGalvaoMVP | pedrogalvaojunior.wordpress.com


    segunda-feira, 29 de dezembro de 2014 16:18
    Moderador
  • Roberto,

          A minha pergunta é: "qual a solução para este problema e porque isso acontece?"

          Quanto a administração do ambiente, preciso devolver espaço para o SO para outras aplicações futuras, então tenho que manter o ambiente administrado.

          Não tenho necessidade que meu arquivo de log para este Banco de Dados seja de 7GB. O inicial com 1GB chegando ao máximo de 4GB e mais do que o suficiente para este Banco de Dados que estou administrando.

          Espaço em disco desperdiçado gera custo para a empresa.

    Jurandyr F. Alves


    • Editado jfonseca segunda-feira, 29 de dezembro de 2014 16:23
    segunda-feira, 29 de dezembro de 2014 16:21
  • Jurandyr,

           Entendo seu ponto de vista, espaço desperdiçado é custo. Entretanto, discordo que seja um problema. Problema é a falta de espaço na base. E também é custo. E talvez o custo seja maior que o espaço (teoricamente) desperdiçado. Vamos imaginar uma situação hipotética. Se você mantiver o seu TL em 4GB e consumir todo o espaço com outras aplicações, no momento que o seu TL precisar crescer para 7GB (e ele vai precisar, pode ter certeza disso, você já viu que ele precisou agora) você não terá os 3Gb disponíveis para ele crescer. Não é você que determina se precisa de 4Gb ou 7Gb ou mais é o espaço disponível no TL que exige que ele cresça mais ou menos. O SQL então utilizará todos os espaços disponíveis na base de dados e não permitirá mais nenhum insert/update/delete na base. Uma das formas de você limitar o tamanho do seu TL é limitar o crescimento ao tamanho máximo desejado, no seu caso, 4Gb, porém, novamente, se o SQL precisar de mais espaço e não estiver disponível, ele não permitirá mais nenhum insert/update/delete na sua base. Qual o custo da sua base ficar parada por 1h ou mais tempo? Notou que isso é o problema REAL?

           Por este motivo, Eu costumo dizer que o TL tem que ter o tamanho suficiente para ele, seja 1Gb, 10Gb ou 50Gb... 

           Pois bem, respondendo as suas perguntas (vou invertê-las para melhor explicar):

           2-Porque o TL cresce? 

               Para que o SQL possa atender as regras ACID (Atomicidade, Consistencia, Isolação, Durabilidade) a cada comando de INSERT, UPDATE, DELETE ele registra esse comando no TL (arquivo .LDF) exatamente na ordem que eles aconteceram e também na memória mas antes que essa informação seja refletida nas tabelas da base de dados (arquivo .MDF) . Isso significa que o dado que está na tabela é diferente do que realmente está na memória?? Sim. É exatamente isso! Mas vamos deixar esse conceito de Clean/Dirty Page para outro momento...  Creio que neste ponto você já entendeu porque ele cresce. Só que as coisas não são tão fáceis assim...

                O SQL trabalha internamente no TL com um conceito chamado VLF (Virtual Log File). Isso singnifica que o seu TL possui internamente dezenas (e as vezes milhares) de VLFs... O shrink só libera espaço correspondente a um VLF quando o VLF está liberado, ou seja, todas as transações já foram registradas nas devidas tabelas (lembra que no princípio os dados das tabelas estava diferentes do TL??) E isso acontece em diversos momentos, mas, normalmente depois de um CHECKPOINT. Imaginando que a sua base esteja em Recovery Model Simple, quando acontece o CHECKPOINT o SQL libera o espaço utilizado pela transação para ser reutilizado por outra transação e com isso reutiliza quase sempre poucos VLFs. Isso corresponde ao seu TL quase sempre ficar muito pequeno, normalmente com o tamanho original da criação do TL (1MB). Caso a sua base esteja em Recovery Model Full, o Checkpoint libera o VLF, mas mantem o espaço utilizado (aquilo que você chamou de despedício, mas o correto é espaço disponível no TL) no VLF até que haja a garantia que o dado estará preservado (Regra ACID de Durabilidade). E neste caso, ele só estará preservado quando for feito um backup. O Backup que libera o espaço utilizado no VLF é o backup do Transaction Log.

         1-Qual a solução para este problema?

              Conforme expliquei acima, esse comportamento do SQL Server não é um problema, mas é uma feature, e uma das mais importantes, que garante que os dados da sua base não serão perdidos. Ou seja, a sua pergunta é: Qual a solução correta para esse comportamento do SQL? Simples! Mantenha uma boa estratégia de Backup! Se a sua base estiver em Recovery Model simple, as opções são o backup Full e Differential, e se estiver como Recovery Model Full, você além de ter o backup Full e Differential, ainda (obrigatoriamente) terá que ter backup do Transaction Log.

        Espero ter ajudado!


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008





    segunda-feira, 29 de dezembro de 2014 17:43
    Moderador
  • Senhores,

    Gostaria apenas de fazer alguns esclarecimentos... 

    1 - Assino embaixo de tudo o que o Roberto Fonseca falou. Este espaço não é "desperdício", é utilização do SQL Server para o bom funcionamento do mesmo. Se por qualquer motivo não precisa do Log de Transações (MUITO CUIDADO NESTE PONTO)... Alterar seu Recovery Model para Simple, deve reduzir consideravelmente seu esforço administrativo, bem como suas possibilidades de recuperação em caso de problemas em sua base.

    2 - Júnior Galvão,   "pois todo processo de Shrink é passível de ocasionar perdas de dados."

    Sua afirmação é perigosa, eu particularmente desconheço um cenário onde um Shrink possa ocasionar perda de dados... Se possível, para esclarecimento meu e de quaisquer outros que tenham dúvidas aqui nesta thread, gostaria que você explicasse como isto seria possível.

    Abraços,

    Edvaldo Castro

    http://edvaldocastro.com

    terça-feira, 30 de dezembro de 2014 14:45
  • Olá Senhores,

    Muito interessante essa discussão, e deixo aqui minhas considerações.

    Concordo com tudo o que o Roberto disse, e como foi mencionado isso não é um problema. Porém, uma coisa me chamou a atenção foi a afirmação do Jurandyr em que ele afirma não precisar de todo este espaço de log. Bom, se você não precisa de todo este espaço, você já se perguntou porque o log precisou crescer até lá? O File Growth está  alto? Há transações abertas há muito tempo? 

    Em complemento as excelentes explicações, recomendo que você verifique este tipo de coisa, principalmente a questão de transações abertas há muito tempo segurando recursos desta base. Digo isso porque mesmo no recovery model SIMPLE, se há uma transação aberta que por algum motivo nunca é encerrada (por COMMIT ou ROLLBACK) o SQL vai segurar o log do mesmo jeito, impedindo o reuso do mesmo e ocasionando  o crescimento a medida que for necessário, uma vez que existe a possibilidade de um ROLLBACK ocorrer, o que faz se necessário o uso dos logs records a partir desta transação. O comando DBCC OPENTRAN pode ser útil neste ponto uma vez que ele te informa qual é a transação mais antiga aberta.

    Ainda assim, concordando com  o Roberto, este não é um problema do SQL. Isso é uma característica do mesmo. Supondo que este fosse o caso de uma transação aberta, o problema seria a resposta desta pergunta: "Por que esta transação está aberta a tanto tempo?". Sempre que o SQL precisar fazer uma operação que requer escrita no log, ele vai exigir que haja espaço disponível. Se não houver, ele tenta crescer. Se não puder crescer ou não houver espaço no log a operação falha.



    []'s | Rodrigo Ribeiro Gomes | MCTS/MCITP Dev/DBA

    terça-feira, 30 de dezembro de 2014 15:15