none
Arquivo de log is full RRS feed

  • Pergunta

  • Olá Pessoal,

    Há algum tempo meu banco de dados vem crescendo (log, ldf), porém quando a menssagem informa: The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases. Até onde sei, informo um auto-grow maior, e também aumento o tamanho do arquivo de log, porém ainda continua aumentar, de 4 em dias essa mensagem aparece (Visualizar de Eventos).

    Observei que alguns comentários existem a respeito de alterar o modo de FULL para SIMPLE, SHRINK, e alterar para FULL novamente, porém isso corre o risco das minhas páginas ficarem fragmentadas. Nesse caso o que vocês me sugerem a fazer? 

    segunda-feira, 8 de agosto de 2011 11:15

Todas as Respostas

  • Bom Dia,

    O log enche porque você está usando o FULL RECOVERY MODE (ou o Bulk Logged). Isso faz com que o log encha indefinidamente até consumir todo o espaço em disco. Nesse caso, a única forma correta de diminuí-lo é efetuando o backup de log. No momento que você faz o backup de log, as entradas de log vão ser copiadas para o arquivo de backup. Como seu LDF está muito grande, após o backup você terá que efetuar um SHRINK no arquivo de log, mas apenas essa primeira vez. Quando você efetuar o backup de log em intervalos regulares, o arquivo não irá crescer dessa forma, pois, cada backup irá esvaziá-lo liberando espaço para novas entradas de log. A cópia dos logs é importante, pois, possibilita que você use-os em um processo de restauração. O Restore dos logs permite que você especifique o horário exato em que o processo de restauração pode parar. O ônus administrativo é maior, mas as possibilidades de restore são maiores.

    Outra possibilidade é mudar para o Recovery Model Simple. Nesse recovery model, após as entradas de log serem efetivadas nos arquivos de dados (MDF), elas são descartadas. O log então só manterá as entradas que ainda não foram contempladas no banco de dados e ficará com um tamanho bem reduzido. Em contrapartida, como o log não está sendo arquivo e é descartado automaticamente, você não poderá utilizá-lo em processos de restore e aí só poderá restaurar seus backups na posição de um backup full ou de um diferencial. Se você tem um full às 8h e um diferencial às 12h, não será possível voltar o banco na situação de 10h por exemplo. Menos administração, mas menos possibilidades de recuperação.

    Agora é escolher o que você deseja e optar pelo Recovery Model adequado ao seu ambiente. Independente da escolha, nesse primeiro momento será necessário efetuar um SHRINK, pois, o arquivo LDF deve estar gigante. O uso do SHRINK introduz fragmentações, mas no arquivo de log isso será pouco relevante, pois, as entradas serão descartadas (ou com a mudança do Recovery Model ou com o backup de log). Só é importante deixar o arquivo de log com um tamanho razoável para caber suas transações (até o backup) e contemplar uma sobra também.

    O que você NÃO DEVE FAZER em hipótese nenhuma é truncar o log ou mudar o Recovery Model para SIMPLE e depois para FULL. Resolvem o problema de espaço, mas truncam o log e impedem a restauração futura. Nesse caso é melhor deixar em SIMPLE mesmo, caso você não queira os logs.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 8 de agosto de 2011 11:53
  • Just,

     

    De uma olhada em meu blog, escrevi um post sobre esse assunto que creio que possa te ajudar: http://fabrizziocaputo.wordpress.com/2011/07/14/sql-server-basico-1-recovery-models/


    Fabrizzio A. Caputo
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    segunda-feira, 8 de agosto de 2011 11:55
    Moderador
  • Gustavo,

    Mas o que acontece, como são 3 banco de dados, e atualmente o tamanho deles é: 1º - 46GB, 2º - 35GB, 3º 20GB - Não seria interessante eu alterar o Recovery Model para SIMPLE, Se eu entendi, o que devo fazer nesse momento é aumentar a frequência de logs de backup, por ex: se eu tenho um backup log a cada 30 minutos, diminuo para 15, é isso?

    Em relação ao auto-grow, e seu tamanho inicial, vai depender de cada ambiente claro (crescimento dia-a-dia mdf, ldf), mas desde já eu considero as minhas considerações boas, e então acho que a única coisa que me falta é aumentar a frequência de backup log, por que veja,

    BD46GB:

    Auto-grow-data: 500MB / Initial Size: 43881
    Auto-grow-log: 700MB /  Initial Size: 3078


    segunda-feira, 8 de agosto de 2011 12:07
  • Bom Dia,

    O tamanho do banco de dados não influencia se você deve ou não fazer um backup de log. Decidir se o backup de log é ou não necessário depende do até quando você tolera perder dados no caso de uma catástrofe ou falha acidental, pois, se não há log, você pode não conseguir voltar no momento em que gostaria. Em contrapartida, lidar com backups de log irá consumir mais espaço e será necessário mais administração. É escolher entre mais trabalho e mais possibilidades (FULL) ou menos trabalho e menos possibilidades (SIMPLE). Esse raciocínio que você deve ter em mente quando for optar pelo Recovery Model.

    Se você já faz backup de log (estou falando de backup de log e não de backup full), então automaticamente o seu Recovery Model não é SIMPLE. Para o seu log encher mesmo com backup de log é porque possivelmente a frequência está muito baixa (digamos um log por semana). Nessa situação, o arquivo irá acumular as transações até que o backup seja feito. Se é feito uma vez por semana, é uma semana inteira de logs. Considere fazê-lo em intervalos menores como duas horas por exemplo. Assim, seu log acumulará no máximo duas horas de log e ficará em um tamanho mais administrável.

    Você poderia nos informar qual é o Recovery Model da base e qual a frequência que o backup de log está sendo realizado ?

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 8 de agosto de 2011 12:17
  • Base: DB001
    Tamanho: 46GB
    Recovery Model: Full 

    BKP FULL
    Dias da semana: Diariamente
    Frequência: 1 vez no dia as 20:00 

    BKP DIFF
    Dias da semana: Segunda á Sexta
    Frequência: a cada 3 horas

    BKP LOG
    Dias da semana: Segunda á Sexta
    Frequência: a cada 30 minutos

    ----

    Base: BD002
    Tamanho: 11GB
    Recovery  Model: Full

    BKP FULL
    Dias da semana: Diariamente
    Frequência: 1 vez no dia as 20:00 

    BKP DIFF
    Dias da semana: Segunda á Sexta
    Frequência: a cada 3 horas

    BKP LOG
    Dias da semana: Segunda á Sexta
    Frequência: a cada 30 minutos

    --- 

    Base: BD003
    Tamanho: 5GB
    Recovery Model: Full 

    BKP FULL
    Dias da semana: Diariamente
    Frequência: 1 vez no dia as 20:00 

    BKP DIFF
    Dias da semana: Segunda á Sexta
    Frequência: a cada 3 horas


    BKP LOG
    Dias da semana: Segunda á Sexta
    Frequência: a cada 30 minutos 

    OBS: Antes de cada BACKUP LOG, efetuo a operação CHECKPOINT (para cada banco de dados), e faço o BACKUP LOG.

    segunda-feira, 8 de agosto de 2011 12:33
  • Bom Dia,

    Me parece que sua política está adequada para não deixar o log encher. Se mesmo com ela o log está enchendo, é por que certamente há algo errado. Você pode descobrir a causa do erro na sys.databases na coluna log_reuse_wait_desc. Normalmente quando a política está adequada e o log enche mesmo assim, há algumas causas comuns:

    - Alguma transação está aberta por muito tempo (típico de desenvolvedores que usam o QA ou o SSMS) e impede que o log seja reutilizado
    - Dados participantes de um processo de replicação transacional que não foram replicados (normalmente quando há indisponibilidade da ponta)
    - Alguma rotina que está em loop. Uma rotina de update em loop pode não fazer diferença para o MDF, mas para o LDF ela irá aumentá-lo

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 8 de agosto de 2011 12:40
  • Gustavo,

    Visualizei a coluna conforme me foi dita, e observei que consta: NOTHING (apenas isso), e em relação as suas considerações:

    - Alguma transação está aberta por muito tempo (típico de desenvolvedores que usam o QA ou o SSMS) e impede que o log seja reutilizado

    Acho que não devido a eu utilizar o procedimento sp_who2, e mais alguns para verificar as consultas que estão em abertas, ou algo do gênero


    - Dados participantes de um processo de replicação transacional que não foram replicados (normalmente quando há indisponibilidade da ponta)

    Nesse Servidor não tenho processo de replicação


    - Alguma rotina que está em loop. Uma rotina de update em loop pode não fazer diferença para o MDF, mas para o LDF ela irá aumentá-lo

    Há possibilidades de identificar se existe no momento? Se sim, e em relação ao seu crescimento voltado para o arquivo de log, teria como visualizar também?

    Eu achei estranho a mensagem de erro, por que como você disse, a política está adequada para não deixar o log encher, porém ainda está enchendo..

    mas vamos vê o que temos ainda..

    segunda-feira, 8 de agosto de 2011 12:53
  • Olá JustSQL,

    Se a coluna está como Nothing então teoricamente não há nada que impeça a redução do log. Vejamos qual é o espaço livre dentro dele:

    SELECT * FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Percent Log Used' And instance_name = DB_NAME()

    Rode essa consulta no seu banco de dados e informe o resultado

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 8 de agosto de 2011 14:00
  • object_name                          counter_name             instance_name cntr_value  cntr_type  

    SQLServer:Databases              Percent Log Used         DB_S001     0         6579265792

     

    Gustavo,

    Não será por causa da minha estratégia de reindex?

    Não sei se Recovery Model Full pode afetar o de log nesse caso, mas atualmente está assim o FULL

    BKP FULL - DB001,DBS002,DBS003 - 01:00 AM (Garantir que as operações posteriores não dê problema)

    REBUILD  -  DB001,DBS002,DBS003 - 01:00 AM (Dúvida: Rebuild  pode causar algum efeito ou deixar o banco inoperante?)

    UPDATE STASTICS -  DB001,DBS002,DBS003 - 01:00 AM (Dúvida: Update Statistics pode causar algum efeito ou deixar o banco inoperante?)

    DELETA BKP FULL -  DB001,DBS002,DBS003 - 01:00 AM 

    BKP FULL 2 -  DB001,DBS002,DBS003 - 01:00 AM

    segunda-feira, 8 de agosto de 2011 14:06
  • Olá JustSQL,

    Se o percentual está zerado, você pode prosseguir com o SHRINK normalmente. Ele irá reduzir o tamanho do arquivo independente do Recovery Model já que há espaço para isso. As rotinas de reindexação podem sim estourar o log já que são "FULL LOGGED". Não podemos deixar de reindexar, mas seria bom fazer um backup de log intercalado entre grandes tabelas. Se você reindexar o banco inteiro e não tiver nenhum log nesse meio tempo, pode esperar um backup de log gigantesco e o crescimento do arquivo.

    You may notice an increased transaction log sizes in SQL Server 2008 and later versions when you perform Index Maintenance
    http://support.microsoft.com/kb/2407439

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 8 de agosto de 2011 14:32
  •  Você diz ter um job para cada arquivo de log de cada tabela em intervalores diferentes? por ex:

    bd001_log - backup log a cada 25 minutos,

    bd002_log - backup log a cada 30 minutos,

    bd003_log - backup log a cada 35 minutos?

    segunda-feira, 8 de agosto de 2011 15:00