Usuário com melhor resposta
SQL 2005 - Arquivo LDF grande, onde o SQL não libera

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
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_descFROM 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/
- Editado Luciano Marson sexta-feira, 28 de fevereiro de 2014 22:21
- Marcado como Resposta Junior Galvão - MVPMVP, Moderator sábado, 1 de março de 2014 14:06
-
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
- Marcado como Resposta Junior Galvão - MVPMVP, Moderator sábado, 1 de março de 2014 14:04
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
- Marcado como Resposta Junior Galvão - MVPMVP, Moderator sábado, 1 de março de 2014 14:04
-
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
-
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
-
-
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
-
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. -
-
Pessoal,
Eu descobri esta query ele
SELECT name, log_reuse_wait_descFROM sys.databases
Ela me retornou que o banco de dados esta com o log grande devido replicação.
master NOTHINGtempdb ACTIVE_TRANSACTIONmodel NOTHINGmsdb NOTHINGteste REPLICATIONAlguma dica se é realmente isso? como eu desabilito isso e libero este log?
-
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_descFROM 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/
- Editado Luciano Marson sexta-feira, 28 de fevereiro de 2014 22:21
- Marcado como Resposta Junior Galvão - MVPMVP, Moderator sábado, 1 de março de 2014 14:06
-