none
Log Muito Grande RRS feed

  • Pergunta

  • Pessoal, bom dia.

    Sou iniciante na parte de banco de dados e estou com um problema que não consigo resolver (SQL Server 2008 R2 64).
    O log de minha base está crescendo muito, o que está deixando o meu arquivo de backup com um tamanho enorme.
    Já tentei os seguintes procedimentos sem sucesso:

    1)

    --Esta Query é para diminuir o tamanho do log SQL server 2008.
    USE BANCO_DE_DADOS;
    GO
    --Truncar o log, alterando o modelo de recuperação de dados para SIMPLE.
    ALTER DATABASE BANCO_DE_DADOS
    SET RECOVERY SIMPLE;
    GO
    -- Reduzir o log truncado arquivo de 1 MB.
    DBCC SHRINKFILE (2, 1);  -- aqui 2 é o arquivo de ID para o arquivo de log trasaction, você também pode mencionar o nome do arquivo de log (dbname_log)
    GO
    -- Redefinir o modelo de recuperação de dados.
    ALTER DATABASE BANCO_DE_DADOS
    SET RECOVERY FULL;
    GO

    2)

    BACKUP LOG BANCO_DE_DADOS TO DISK = ‘NUL:’


    Nenhuma das duas maneiras diminui o arquivo de log. De que forma devo proceder para "forçar" a redução do arquivo? Existe alguma rotina que eu possa executar diariamente? Existe replicação na minha estrutura.

    Grato a todos!

    sexta-feira, 20 de novembro de 2015 10:48

Todas as Respostas

  • Adauto,

    Vamos por partes:

    Este atual banco de dados que você esta utilizando possui qual tamanho?

    Durante este procedimento que você esta realizando você verificou se não existe nenhuma transação que possa esta fazendo uso do banco durante a tentativa de reduzir o tamanho do arquivo.

    Antes de você fazer qualquer tipo de procedimento deste tipo é altamente recomendável realizar o backup do seu banco de dados, bem como, executar este script abaixo para identificar se não existe nenhuma transação que possa estar fazendo uso do arquivo de log:

    -- Do not lock anything, and do not get held up by any locks.
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
    -- What SQL statements are currently using the transaction logs?
    SELECT tst.session_id
      , es.original_login_name
      , DB_NAME(tdt.database_id) AS DatabaseName
      , DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS [TransDuration(s)]
      , tdt.database_transaction_log_record_count AS SpaceUsed
      , CASE tat.transaction_state
          WHEN 0 THEN 'The transaction has not been completely initialized yet'
          WHEN 1 THEN 'The transaction has been initialized but has not started'
          WHEN 2 THEN 'The transaction is active'
          WHEN 3 THEN 'The transaction has ended'
          WHEN 4 THEN 'The commit process has been initiated on the distributed tran'
          WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution'
          WHEN 6 THEN 'The transaction has been committed'
          WHEN 7 THEN 'The transaction is being rolled back'
          WHEN 8 THEN 'The transaction has been rolled back'
          ELSE 'Unknown'
      END AS TransactionState
      , SUBSTRING(TXT.text, ( er.statement_start_offset / 2 ) + 1,
           ( ( CASE WHEN er.statement_end_offset = -1
                         THEN LEN(CONVERT(NVARCHAR(MAX), TXT.text)) * 2
                         ELSE er.statement_end_offset
                  END - er.statement_start_offset ) / 2 ) + 1) AS CurrentQuery
      , TXT.text AS ParentQuery
      , es.host_name
      , CASE tat.transaction_type
          WHEN 1 THEN 'Read/Write Transaction'
          WHEN 2 THEN 'Read-Only Transaction'
          WHEN 3 THEN 'System Transaction'
                  WHEN 4 THEN 'Distributed Transaction'
                  ELSE 'Unknown'
      END AS TransactionType
      , tat.transaction_begin_time AS StartTime
    FROM sys.dm_tran_session_transactions AS tst
           INNER JOIN sys.dm_tran_active_transactions AS tat
                  ON tst.transaction_id = tat.transaction_id
           INNER JOIN sys.dm_tran_database_transactions AS tdt
                  ON tst.transaction_id = tdt.transaction_id
           INNER JOIN sys.dm_exec_sessions es
                  ON tst.session_id = es.session_id
           INNER JOIN sys.dm_exec_requests er
                  ON tst.session_id = er.session_id
           CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT
    --ORDER BY tdt.database_transaction_log_record_count DESC -- log space size.
    ORDER BY [TransDuration(s)] DESC -- transaction duration.

    Após isso, você deverá realizar o procedimento que você esta atualmente testando, mas acrescentar a instrução TruncateOnly, justamente para truncar de forma real o log e liberar os espaços não alocados, vejo este exemplo:

    --Diminuindo o Tamanho do Arquivo de Log e liberando espaços existentes --
    DBCC ShrinkFile('Cipa_LOG',TruncateOnly)
    
    --Diminuindo o Tamanho do Arquivo de Log para 1 MB --
    DBCC ShrinkFile('Cipa_LOG',1)
    
    --Fazendo o Backup do Log e liberando espaços --
    BACKUP LOG CIPA WITH TRUNCATE_ONLY
    
    


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 20 de novembro de 2015 17:37
    Moderador