none
SQL 2005 - Arquivo LDF grande, onde o SQL não libera RRS feed

  • Pergunta

  • Boa Tarde Pessoal,

    Estou com uma situação muito estranha e não consegui resolver ainda...

    Tenho um servidor com o SQL 2005 e um banco de dados cujo o arquivo MDF esta com 3GB e o arquivo LDF esta com 26GB.

    Já fiz de tudo e não há meio deste log diminuir 

    - Shirink do log

    - Recovery Full + backup full + backup log + shirink

    - Shrinrk com a opção truncate;

    - DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS

    - Reinicialização da instancia

    - Reinicialização do Servidor;

    - Banco OffLine e depois OnLine;

    - Copia do banco para uma outra maquina com o SQL 2005;

    Alguém tem alguma dica do que posso tentar fazer para diminuir este log?

    Obs. Se eu faço o backup para uma base de teste por exemplo o log também fica grande e não libera também.

    Obrigado


    quinta-feira, 27 de fevereiro de 2014 20:54

Respostas

  • Pessoal, Boa Tarde

    Consegui resolver a questão!!

    é o seguinte...alguem tentou configurar a replicação no SQL 2005 e fez pela metade, só que ninguém falou nada sobre isso.

    Descobri que o tamanho do log era devido a replicação com a utilização deste comando:

    SELECT name, log_reuse_wait_desc

    FROM sys.databases

    Depois de descoberto a origem do problema sai na net procurando como desabilitar isso e encontrei esta procedure

    sp_removedbreplication 'DB_PROD' go

    Depois de removida a replicação ao rodar a query anterior o SQL não me retornava que estava com replicação, com isso resolvi seguir o procedimento passado pelo Felipe (exatamente isso), depois disso verifiquei que o log caiu para 1GB.

    Queria agradecer a todos!! 

    Obrigado

    Obs. O comando sobre a replicação eu peguei neste site (muito bacana os procedimentos)

    http://www.techrepublic.com/blog/the-enterprise-cloud/help-my-sql-server-log-file-is-too-big/


    sexta-feira, 28 de fevereiro de 2014 17:16
  • bom dia Luciano... 

    Antes de mais nada, é muito importante entender PORQUE isso aconteceu... 

    O arquivo apesar de se ter o nome log no nome.. é um arquivo MUITO importante. muitas vezes até mais importante que o arquivo de dados.. é onde todas as transações no SQL acontecem... e só depois elas são aplicadas no arquivo de dados... Por isso, o SQL só tira algo do log de transação e começa a reutilizar o espaço interno dele quando você faz o backup do log... então o principal problema é que voce não em uma rotina para fazer de tempo em tempo um backup de log... o que te impossibilita de recuperar o banco até o segundo exato da falha em caso de um desastre, te deixando apenas a opção de recuperar até o ultimo backup completo ou diferencial que voce tenha feito... 

    Mas muito bem.. para resolver agora o seu problema você vai ter que fazer os seguintes passos: 

    1 Realizar um backup do log

    2 Alterar o modo de trabalho da base de dados de FULL para Simple 

    http://technet.microsoft.com/en-us/library/ms189272.aspx

    3 Executar o comando CHECKPOINT

    4 Realizar o shrink do arquivo de LOG para que ele libere o espaço para o sistema operacional

    5 Voltar a base para o modo de recuperação FULL

    6 Fazer um backup completo da base. 

    Para os passos de 2 a 6 o script para isso ficaria assim: 

    USE AdventureWorks2012; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE; GO

    CHECKPOINT

    GO -- Shrink the truncated log file to 1024 MB. DBCC SHRINKFILE (AdventureWorks2012_Log, 1024); GO -- Reset the database recovery model. ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO



    Felipe Ferreira - @SQLBoy SQL Server MVP | SolidQ Mentor | Friends of RedGate http://www.templar.com.br/blogs/felipe

    sexta-feira, 28 de fevereiro de 2014 10:28

Todas as Respostas

  • bom dia Luciano... 

    Antes de mais nada, é muito importante entender PORQUE isso aconteceu... 

    O arquivo apesar de se ter o nome log no nome.. é um arquivo MUITO importante. muitas vezes até mais importante que o arquivo de dados.. é onde todas as transações no SQL acontecem... e só depois elas são aplicadas no arquivo de dados... Por isso, o SQL só tira algo do log de transação e começa a reutilizar o espaço interno dele quando você faz o backup do log... então o principal problema é que voce não em uma rotina para fazer de tempo em tempo um backup de log... o que te impossibilita de recuperar o banco até o segundo exato da falha em caso de um desastre, te deixando apenas a opção de recuperar até o ultimo backup completo ou diferencial que voce tenha feito... 

    Mas muito bem.. para resolver agora o seu problema você vai ter que fazer os seguintes passos: 

    1 Realizar um backup do log

    2 Alterar o modo de trabalho da base de dados de FULL para Simple 

    http://technet.microsoft.com/en-us/library/ms189272.aspx

    3 Executar o comando CHECKPOINT

    4 Realizar o shrink do arquivo de LOG para que ele libere o espaço para o sistema operacional

    5 Voltar a base para o modo de recuperação FULL

    6 Fazer um backup completo da base. 

    Para os passos de 2 a 6 o script para isso ficaria assim: 

    USE AdventureWorks2012; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE; GO

    CHECKPOINT

    GO -- Shrink the truncated log file to 1024 MB. DBCC SHRINKFILE (AdventureWorks2012_Log, 1024); GO -- Reset the database recovery model. ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO



    Felipe Ferreira - @SQLBoy SQL Server MVP | SolidQ Mentor | Friends of RedGate http://www.templar.com.br/blogs/felipe

    sexta-feira, 28 de fevereiro de 2014 10:28
  • Bom Dia, Felipe

    Obrigado pelo retorno, vou fazer da maneira que você explicou seguindo estes passos.

    Apenas uma informação, apesar do banco estar com o log de 26GB o recovery model dele esta como simple e isso que me deixa mais intrigado.

    Nos testes anteriores que eu havia feito, eu tinha colocado ele como FULL, feito o backup full e depois o backup do LOG, porem sem sucesso. Sendo que o backup do LOG ficou com um tamanho aproximado de 26GB.

    Obrigado

    sexta-feira, 28 de fevereiro de 2014 11:56
  • Bom Dia, Felipe

    Obrigado pelo retorno, vou fazer da maneira que você explicou seguindo estes passos.

    Apenas uma informação, apesar do banco estar com o log de 26GB o recovery model dele esta como simple e isso que me deixa mais intrigado.

    Nos testes anteriores que eu havia feito, eu tinha colocado ele como FULL, feito o backup full e depois o backup do LOG, porem sem sucesso. Sendo que o backup do LOG ficou com um tamanho aproximado de 26GB.

    Obrigado

    O seu LDF não esta como tamanho inicial os 26gb (INITIAL SIZE)?
    Havia uma base de desenvolvimento no meu ambiente em que alguém acabou deixando o LDF com 10GB e demorei um tempo para perceber.

    []´s

    sexta-feira, 28 de fevereiro de 2014 14:12
  • Obrigado novamente

    Ele esta com o tamanho de 26GB, mas este tamanho é o tamanho que esta agora, eu não posso alterar este tamanho, pois o SQL me diz que eu tenho 25GB de informação neste log, onde é isso que esta me matando. 

    Att



    sexta-feira, 28 de fevereiro de 2014 14:31
  • bom dia Luciano... 

    Antes de mais nada, é muito importante entender PORQUE isso aconteceu... 

    O arquivo apesar de se ter o nome log no nome.. é um arquivo MUITO importante. muitas vezes até mais importante que o arquivo de dados.. é onde todas as transações no SQL acontecem... e só depois elas são aplicadas no arquivo de dados... Por isso, o SQL só tira algo do log de transação e começa a reutilizar o espaço interno dele quando você faz o backup do log... então o principal problema é que voce não em uma rotina para fazer de tempo em tempo um backup de log... o que te impossibilita de recuperar o banco até o segundo exato da falha em caso de um desastre, te deixando apenas a opção de recuperar até o ultimo backup completo ou diferencial que voce tenha feito... 

    Mas muito bem.. para resolver agora o seu problema você vai ter que fazer os seguintes passos: 

    1 Realizar um backup do log

    2 Alterar o modo de trabalho da base de dados de FULL para Simple 

    http://technet.microsoft.com/en-us/library/ms189272.aspx

    3 Executar o comando CHECKPOINT

    4 Realizar o shrink do arquivo de LOG para que ele libere o espaço para o sistema operacional

    5 Voltar a base para o modo de recuperação FULL

    6 Fazer um backup completo da base. 

    Para os passos de 2 a 6 o script para isso ficaria assim: 

    USE AdventureWorks2012; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE; GO

    CHECKPOINT

    GO -- Shrink the truncated log file to 1024 MB. DBCC SHRINKFILE (AdventureWorks2012_Log, 1024); GO -- Reset the database recovery model. ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO



    Felipe Ferreira - @SQLBoy SQL Server MVP | SolidQ Mentor | Friends of RedGate http://www.templar.com.br/blogs/felipe

    Felipe, Bom Dia

    Eu fiz o procedimento conforme você me passou, e utilizei os comandos que vc colocou (apenas mudei o nome do banco de dados), infelizmente não liberou o espaço em disco

    DBID / FILD / CURRENTSIZE / MIMIMUN SIZE / USED PAGES / ESTIMATED PAGES

    5 2 3240248 63 3240248 56

    Quanto aos backups de log, o primeiro ficou com 26GB os demais ficaram bem pequeno 3MB (rodei uns 5 de log).

    Porem apôs rodar os comandos não tive resultado, onde o SHIRINK deu como retorno o resultado acima.

    Att

    sexta-feira, 28 de fevereiro de 2014 14:47
  • Obrigado novamente

    Ele esta com o tamanho de 26GB, mas este tamanho é o tamanho que esta agora, eu não posso alterar este tamanho, pois o SQL me diz que eu tenho 25GB de informação neste log, onde é isso que esta me matando. 

    Att



    Opa, de nada :)

    Chegou a efetuar os passos que o Felipe demonstrou no post #2?
    Se executar e não funcionar, eu checaria também se não tem algum outro processo que pode estar onerando este seu log. 

    sexta-feira, 28 de fevereiro de 2014 14:53
  • Sim executei e não resolveu.

    Quanto a processo, entendo que eu já havia até reiniciado o servidor, com isso as transações pendentes seriam eliminadas.

    Obrigado

    sexta-feira, 28 de fevereiro de 2014 15:53
  • Pessoal,

    Eu descobri esta query ele

    SELECT name, log_reuse_wait_desc

    FROM sys.databases

    Ela me retornou que o banco de dados esta com o log grande devido replicação.

    master     NOTHING
    tempdb     ACTIVE_TRANSACTION
    model     NOTHING
    msdb     NOTHING
    teste     REPLICATION 

    Alguma dica se é realmente isso? como eu desabilito isso e libero este log?

    sexta-feira, 28 de fevereiro de 2014 16:59
  • Pessoal, Boa Tarde

    Consegui resolver a questão!!

    é o seguinte...alguem tentou configurar a replicação no SQL 2005 e fez pela metade, só que ninguém falou nada sobre isso.

    Descobri que o tamanho do log era devido a replicação com a utilização deste comando:

    SELECT name, log_reuse_wait_desc

    FROM sys.databases

    Depois de descoberto a origem do problema sai na net procurando como desabilitar isso e encontrei esta procedure

    sp_removedbreplication 'DB_PROD' go

    Depois de removida a replicação ao rodar a query anterior o SQL não me retornava que estava com replicação, com isso resolvi seguir o procedimento passado pelo Felipe (exatamente isso), depois disso verifiquei que o log caiu para 1GB.

    Queria agradecer a todos!! 

    Obrigado

    Obs. O comando sobre a replicação eu peguei neste site (muito bacana os procedimentos)

    http://www.techrepublic.com/blog/the-enterprise-cloud/help-my-sql-server-log-file-is-too-big/


    sexta-feira, 28 de fevereiro de 2014 17:16
  • Interessante essa thread :)

    Luciano, marca o post que resolveu a questão para marcar a thread como Resolvida :D

    E valeu por compartilhar o link de referência :)

    Até mais,


    TP.

    sexta-feira, 28 de fevereiro de 2014 23:02