Partilhar via

Solucionar problemas de um log de transações completo (Erro 9002 do SQL Server)

Aplica-se a:SQL Server

Este artigo aplica-se a instâncias do SQL Server.

Observação

Este artigo é focado no SQL Server. Para obter informações mais específicas sobre esse erro nas plataformas SQL do Azure, consulte Solução de problemas de erros de log de transações com o Banco de Dados SQL do Azure e Solução de problemas de erros de log de transações com a Instância Gerenciada SQL do Azure. O Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure são baseados na versão estável mais recente do mecanismo de banco de dados do Microsoft SQL Server, portanto, grande parte do conteúdo é semelhante, embora as opções e ferramentas de solução de problemas possam diferir.

Opção 1: Executar as etapas diretamente em um bloco de anotações executável por meio do Azure Data Studio

Antes de tentar abrir este bloco de anotações, verifique se o Azure Data Studio está instalado em sua máquina local. Para instalar, vá para Baixar e instalar o Azure Data Studio.

Abrir Bloco de Anotações no Azure Data Studio

Opção 2: Siga os passos manualmente

Este artigo discute possíveis respostas a um log de transações completo e sugere como evitá-lo no futuro.

Quando o log de transações fica cheio, o Mecanismo de Banco de Dados do SQL Server emite um erro 9002. O log pode ser preenchido quando o banco de dados está online ou em recuperação. Se o registo ficar cheio quando a base de dados está online, a base de dados permanecerá online, mas apenas poderá ser lida e não atualizada. Se o log for preenchido durante a recuperação, o Mecanismo de Banco de Dados marcará o banco de dados como RESOURCE PENDING. Em ambos os casos, a ação do usuário é necessária para disponibilizar espaço de log.

Razões comuns para um log de transações completo

A resposta apropriada a um log de transações completo depende das condições que causaram o preenchimento do log. As causas comuns incluem:

  • O log não está sendo truncado
  • O volume do disco está cheio
  • O tamanho do log é definido como um valor máximo fixo ou o crescimento automático está desativado
  • Sincronização de grupo de replicação ou grupo de disponibilidade que não pode ser concluída

Siga estas etapas específicas para ajudá-lo a encontrar o motivo para um log de transações completo e resolver o problema.

Truncar o log

Uma solução habitual para esse problema é garantir que os backups de log de transações sejam feitos no seu banco de dados, o que garante que o log seja truncado. Se nenhum histórico recente do log de transações for indicado para um banco de dados com um log de transações completo, a solução para o problema é simples: retomar os backups regulares do log de transações do banco de dados.

Para obter mais informações, consulte Gerenciar o tamanho do arquivo de log de transações e Reduzir um arquivo.

Explicação do truncamento de log

Há uma diferença entre truncar um log de transações e reduzir um log de transações. O truncamento de log ocorre normalmente durante um backup de log de transações e é uma operação lógica que remove registros confirmados dentro do log, enquanto a redução de log recupera espaço físico no sistema de arquivos reduzindo o tamanho do arquivo. O truncamento de log ocorre numa fronteira de arquivo de log virtual (VLF) e um arquivo de log pode conter diversos VLFs. Um arquivo de log pode ser reduzido somente se houver espaço vazio dentro do arquivo de log para recuperar. Reduzir um arquivo de log sozinho não pode resolver o problema de um arquivo de log completo. Em vez disso, você deve descobrir por que o arquivo de log está cheio e não pode ser truncado.

Advertência

Os dados que são movidos para reduzir um arquivo podem ser espalhados para qualquer local disponível no arquivo. Isso causa fragmentação do índice e pode diminuir o desempenho de consultas que pesquisam um intervalo do índice. Para eliminar a fragmentação, considere reconstruir os índices no arquivo após a redução. Para obter mais informações, consulte Reduzir um banco de dados.

O que está impedindo o truncamento de log?

Para descobrir o que está impedindo o truncamento de log em um determinado caso, use as log_reuse_wait colunas e log_reuse_wait_desc da sys.databases exibição de catálogo. Para obter mais informações, consulte sys.databases. Para obter descrições dos fatores que podem atrasar o truncamento do log, consulte O log de transações.

O conjunto de comandos T-SQL a seguir ajuda a identificar se um log de transações de banco de dados não está truncado e o motivo para isso. O script a seguir também recomenda etapas para resolver o problema:

SET NOCOUNT ON;

DECLARE
    @SQL AS VARCHAR (8000),
    @log_reuse_wait AS TINYINT,
    @log_reuse_wait_desc AS NVARCHAR (120),
    @dbname AS SYSNAME,
    @database_id AS INT,
    @recovery_model_desc AS VARCHAR (24);

IF (OBJECT_id(N'tempdb..#CannotTruncateLog_Db') IS NOT NULL)
    BEGIN
        DROP TABLE #CannotTruncateLog_Db;
    END

--get info about transaction logs in each database.
IF (OBJECT_id(N'tempdb..#dm_db_log_space_usage') IS NOT NULL)
    BEGIN
        DROP TABLE #dm_db_log_space_usage;
    END

SELECT *
INTO #dm_db_log_space_usage
FROM sys.dm_db_log_space_usage
WHERE 1 = 0;

DECLARE log_space CURSOR
    FOR SELECT NAME
        FROM sys.databases;

OPEN log_space;

FETCH NEXT FROM log_space INTO @dbname;

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = '
            INSERT INTO #dm_db_log_space_usage (
                database_id,
                total_log_size_in_bytes,
                used_log_space_in_bytes,
                used_log_space_in_percent,
                log_space_in_bytes_since_last_backup
                )
            SELECT database_id,
                total_log_size_in_bytes,
                used_log_space_in_bytes,
                used_log_space_in_percent,
                log_space_in_bytes_since_last_backup
            FROM ' + QUOTENAME(@dbname) + '.sys.dm_db_log_space_usage;';
        BEGIN TRY
            EXECUTE (@SQL);
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE() AS ErrorMessage;
        END CATCH
        FETCH NEXT FROM log_space INTO @dbname;
    END

CLOSE log_space;
DEALLOCATE log_space;

--select the affected databases
SELECT
    sdb.name AS DbName,
    sdb.log_reuse_wait,
    sdb.log_reuse_wait_desc,
    CASE
        WHEN log_reuse_wait = 1 THEN 'No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond'
        WHEN log_reuse_wait = 2 THEN 'A log backup is required before the transaction log can be truncated.'
        WHEN log_reuse_wait = 3 THEN 'A data backup or a restore is in progress (all recovery models). Please wait or cancel backup'
        WHEN log_reuse_wait = 4 THEN 'A long-running active transaction or a deferred transaction is keeping log from being truncated. You can attempt a log backup to free space or complete/rollback long transaction'
        WHEN log_reuse_wait = 5 THEN 'Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only)'
        WHEN log_reuse_wait = 6 THEN 'During transactional replication, transactions relevant to the publications are still undelivered to the distribution database. Investigate the status of agents involved in replication or Changed Data Capture (CDC). (Full recovery model only.)'
        WHEN log_reuse_wait = 7 THEN 'A database snapshot is being created. This is a routine, and typically brief, cause of delayed log truncation.'
        WHEN log_reuse_wait = 8 THEN 'A transaction log scan is occurring. This is a routine, and typically a brief cause of delayed log truncation.'
        WHEN log_reuse_wait = 9 THEN 'A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model only.)'
        WHEN log_reuse_wait = 13 THEN 'If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN).'
        WHEN log_reuse_wait = 16 THEN 'An In-Memory OLTP checkpoint has not occurred since the last log truncation, or the head of the log has not yet moved beyond a VLF.'
        ELSE 'None'
    END AS log_reuse_wait_explanation,
    sdb.database_id,
    sdb.recovery_model_desc,
    lsu.used_log_space_in_bytes / 1024. / 1024. AS Used_log_size_MB,
    lsu.total_log_size_in_bytes / 1024. / 1024. AS Total_log_size_MB,
    100 - lsu.used_log_space_in_percent AS Percent_Free_Space
INTO #CannotTruncateLog_Db
FROM sys.databases AS sdb
     INNER JOIN #dm_db_log_space_usage AS lsu
         ON sdb.database_id = lsu.database_id
WHERE log_reuse_wait > 0;

SELECT * FROM #CannotTruncateLog_Db;

DECLARE no_truncate_db CURSOR FOR
SELECT
    log_reuse_wait,
    log_reuse_wait_desc,
    DbName,
    database_id,
    recovery_model_desc
FROM #CannotTruncateLog_Db;

OPEN no_truncate_db;

FETCH NEXT FROM no_truncate_db
INTO
    @log_reuse_wait,
    @log_reuse_wait_desc,
    @dbname,
    @database_id,
    @recovery_model_desc;

WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@log_reuse_wait > 0)
        BEGIN
            SELECT '-- ' + QUOTENAME(@dbname) + ' database has log_reuse_wait = ' + @log_reuse_wait_desc + ' --' AS 'Individual Database Report';
        END
        IF (@log_reuse_wait = 1)
        BEGIN
            SELECT 'Consider running the checkpoint command to attempt resolving this issue or further t-shooting may be required on the checkpoint process. Also, examine the log for active VLFs at the end of file' AS Recommendation;
            SELECT 'USE ' + QUOTENAME(@dbname) + '; CHECKPOINT' AS CheckpointCommand;
            SELECT 'SELECT * FROM sys.dm_db_log_info(' + CONVERT (VARCHAR, @database_id) + ')' AS VLF_LogInfo;
        END
        ELSE IF (@log_reuse_wait = 2)
        BEGIN
            SELECT 'Is ' + @recovery_model_desc + ' recovery model the intended choice for ' + QUOTENAME(@dbname) + ' database? Review recovery models and determine if you need to change it. https://learn.microsoft.com/sql/relational-databases/backup-restore/recovery-models-sql-server' AS RecoveryModelChoice;
            SELECT 'To truncate the log consider performing a transaction log backup on database ' + QUOTENAME(@dbname) + ' which is in ' + @recovery_model_desc + ' recovery model. Be mindful of any existing log backup chains that could be broken' AS Recommendation;
            SELECT 'BACKUP LOG ' + QUOTENAME(@dbname) + ' TO DISK = ''some_volume:\some_folder\' + QUOTENAME(@dbname) + '_LOG.trn '';' AS BackupLogCommand;
        END
        ELSE IF (@log_reuse_wait = 3)
        BEGIN
            SELECT 'Either wait for or cancel any active backups currently running for database ' + QUOTENAME(@dbname) + '. To check for backups, run this command:' AS Recommendation;
            SELECT 'SELECT * FROM sys.dm_exec_requests WHERE command LIKE ''backup%'' OR command LIKE ''restore%''' AS FindBackupOrRestore;
        END
        ELSE IF (@log_reuse_wait = 4)
        BEGIN
            SELECT 'Active transactions currently running for database ' + QUOTENAME(@dbname) + '. To check for active transactions, run these commands:' AS Recommendation;
            SELECT 'DBCC OPENTRAN (' + QUOTENAME(@dbname) + ')' AS FindOpenTran;
            SELECT 'SELECT database_id, db_name(database_id) AS dbname, database_transaction_begin_time, database_transaction_state, database_transaction_log_record_count, database_transaction_log_bytes_used, database_transaction_begin_lsn, stran.session_id FROM sys.dm_tran_database_transactions dbtran LEFT OUTER JOIN sys.dm_tran_session_transactions stran ON dbtran.transaction_id = stran.transaction_id WHERE database_id = ' + CONVERT (VARCHAR, @database_id) AS FindOpenTransAndSession;
        END
        ELSE IF (@log_reuse_wait = 5)
        BEGIN
            SELECT 'Database Mirroring for database ' + QUOTENAME(@dbname) + ' is behind on synchronization. To check the state of DBM, run the commands below:' AS Recommendation;
            SELECT 'SELECT db_name(database_id), mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL and mirroring_state <> 4 AND database_id = ' + CONVERT (sysname, @database_id) AS CheckMirroringStatus;
            SELECT 'Database Mirroring for database ' + QUOTENAME(@dbname) + ' may be behind: check unsent_log, send_rate, unrestored_log, recovery_rate, average_delay in this output' AS Recommendation;
            SELECT 'EXECUTE msdb.sys.sp_dbmmonitoraddmonitoring 1; EXECUTE msdb.sys.sp_dbmmonitorresults ' + QUOTENAME(@dbname) + ', 5, 0; WAITFOR DELAY ''00:01:01''; EXECUTE msdb.sys.sp_dbmmonitorresults ' + QUOTENAME(@dbname) + '; EXECUTE msdb.sys.sp_dbmmonitordropmonitoring' AS CheckMirroringStatusAnd;
        END
        ELSE IF (@log_reuse_wait = 6)
        BEGIN
            SELECT 'Replication transactions still undelivered FROM publisher database ' + QUOTENAME(@dbname) + ' to Distribution database. Check the oldest non-distributed replication transaction. Also check if the Log Reader Agent is running and if it has encountered any errors' AS Recommendation;
            SELECT 'DBCC OPENTRAN  (' + QUOTENAME(@dbname) + ')' AS CheckOldestNonDistributedTran;
            SELECT 'SELECT top 5 * FROM distribution..MSlogreader_history WHERE runstatus in (6, 5) OR error_id <> 0 AND agent_id = find_in_mslogreader_agents_table ORDER BY time desc ' AS LogReaderAgentState;
        END
        ELSE IF (@log_reuse_wait = 9)
        BEGIN
            SELECT 'Always On transactions still undelivered FROM primary database ' + QUOTENAME(@dbname) + ' to Secondary replicas. Check the Health of AG nodes and if there is latency is Log block movement to Secondaries' AS Recommendation;
            SELECT 'SELECT availability_group = CAST(ag.name AS VARCHAR(30)), primary_replica = CAST(ags.primary_replica AS VARCHAR(30)), primary_recovery_health_desc = CAST(ags.primary_recovery_health_desc AS VARCHAR(30)), synchronization_health_desc = CAST(ags.synchronization_health_desc AS VARCHAR(30)), ag.failure_condition_level, ag.health_check_timeout, automated_backup_preference_desc = CAST(ag.automated_backup_preference_desc AS VARCHAR(10)) FROM sys.availability_groups ag join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id' AS CheckAGHealth;
            SELECT 'SELECT  group_name = CAST(arc.group_name AS VARCHAR(30)), replica_server_name = CAST(arc.replica_server_name AS VARCHAR(30)), node_name = CAST(arc.node_name AS VARCHAR(30)), role_desc = CAST(ars.role_desc AS VARCHAR(30)), ar.availability_mode_Desc, operational_state_desc = CAST(ars.operational_state_desc AS VARCHAR(30)), connected_state_desc = CAST(ars.connected_state_desc AS VARCHAR(30)), recovery_health_desc = CAST(ars.recovery_health_desc AS VARCHAR(30)), synchronization_health_desc = CAST(ars.synchronization_health_desc AS VARCHAR(30)), ars.last_connect_error_number, last_connect_error_description = CAST(ars.last_connect_error_description AS VARCHAR(30)), ars.last_connect_error_timestamp, primary_role_allow_connections_desc = CAST(ar.primary_role_allow_connections_desc AS VARCHAR(30)) FROM sys.dm_hadr_availability_replica_cluster_nodes arc join sys.dm_hadr_availability_replica_cluster_states arcs on arc.replica_server_name=arcs.replica_server_name join sys.dm_hadr_availability_replica_states ars on arcs.replica_id=ars.replica_id join sys.availability_replicas ar on ars.replica_id=ar.replica_id join sys.availability_groups ag on ag.group_id = arcs.group_id and ag.name = arc.group_name ORDER BY CAST(arc.group_name AS VARCHAR(30)), CAST(ars.role_desc AS VARCHAR(30))' AS CheckReplicaHealth;
            SELECT 'SELECT database_name = CAST(drcs.database_name AS VARCHAR(30)), drs.database_id, drs.group_id, drs.replica_id, drs.is_local, drcs.is_failover_ready, drcs.is_pending_secondary_suspend, drcs.is_database_joined, drs.is_suspended, drs.is_commit_participant, suspend_reason_desc = CAST(drs.suspend_reason_desc AS VARCHAR(30)), synchronization_state_desc = CAST(drs.synchronization_state_desc AS VARCHAR(30)), synchronization_health_desc = CAST(drs.synchronization_health_desc AS VARCHAR(30)), database_state_desc = CAST(drs.database_state_desc AS VARCHAR(30)), drs.last_sent_lsn, drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.low_water_mark_for_ghosts, drs.recovery_lsn, drs.truncation_lsn, pr.file_id, pr.error_type, pr.page_id, pr.page_status, pr.modification_time FROM sys.dm_hadr_database_replica_cluster_states drcs join sys.dm_hadr_database_replica_states drs on drcs.replica_id=drs.replica_id and drcs.group_database_id=drs.group_database_id left outer join sys.dm_hadr_auto_page_repair pr on drs.database_id=pr.database_id  order by drs.database_id' AS LogMovementHealth;
            SELECT 'For more information see https://learn.microsoft.com/troubleshoot/sql/availability-groups/error-9002-transaction-log-large' AS OnlineDOCResource;
        END
        ELSE IF (@log_reuse_wait IN (10, 11, 12, 14))
        BEGIN
            SELECT 'This state is not documented and is expected to be rare and short-lived' AS Recommendation;
        END
        ELSE IF (@log_reuse_wait = 13)
        BEGIN
            SELECT 'The oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation.' AS Finding;
            SELECT 'This state should be short-lived, but if you find it is taking a long time, you can consider disabling Indirect Checkpoint temporarily' AS Recommendation;
            SELECT 'ALTER DATABASE ' + QUOTENAME(@dbname) + ' SET TARGET_RECOVERY_TIME = 0 SECONDS;' AS DisableIndirectCheckpointTemporarily;
        END
        ELSE IF (@log_reuse_wait = 16)
        BEGIN
            SELECT 'For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint (includes both disk-based and memory-optimized tables)' AS Finding;
            SELECT 'Review https://learn.microsoft.com/archive/blogs/sqlcat/logging-and-checkpoint-process-for-memory-optimized-tables-2' AS ReviewBlog;
            SELECT 'USE ' + QUOTENAME(@dbname) + '; CHECKPOINT;' AS RunCheckpoint;
        END
        FETCH NEXT FROM no_truncate_db INTO
            @log_reuse_wait,
            @log_reuse_wait_desc,
            @dbname,
            @database_id,
            @recovery_model_desc;
    END

CLOSE no_truncate_db;
DEALLOCATE no_truncate_db;

Importante

Se o banco de dados estava em recuperação quando ocorreu o erro 9002, depois de resolver o problema, recupere o banco de dados usando ALTER DATABASE database_name SET ONLINE.

LOG_BACKUP log_reuse_wait

A ação mais comum a ser considerada se você vê LOG_BACKUP ou log_reuse_wait é revisar o modelo de recuperação do banco de dados e fazer backup do log de transações do banco de dados.

Considere o modelo de recuperação do banco de dados

O log de transações pode estar falhando ao truncar com LOG_BACKUP ou log_reuse_wait categoria, porque você nunca fez backup dele. Em muitos desses casos, o seu banco de dados está a usar o modelo de recuperação FULL ou BULK_LOGGED, mas não efetuou uma cópia de segurança do log de transações. Você deve considerar cada modelo de recuperação de banco de dados cuidadosamente, executar backups regulares de log de transações em todos os bancos de dados nos modelos de recuperação FULL ou BULK_LOGGED, para minimizar as ocorrências de erro 9002. Para obter mais informações, consulte Modelos de recuperação.

Criar cópia de segurança do log

No modelo de recuperação FULL ou BULK_LOGGED, se o backup do log de transações não tiver sido feito recentemente, isso pode estar a impedir o truncamento do log. Você deve fazer backup do log de transações para permitir que os registros de log sejam liberados e o log truncado. Se nunca tiver sido feito backup do log, você deverá criar dois backups de log para permitir que o Mecanismo de Banco de Dados trunce o log até o ponto do último backup. Truncar o log libera espaço lógico para novos registros de log. Para evitar que o log seja preenchido novamente, faça backups de log regularmente e com mais frequência. Para obter mais informações, consulte Modelos de recuperação.

Um histórico completo de todas as operações de backup e restauração do SQL Server em uma instância de servidor é armazenado no banco de dados do msdb sistema. Para revisar o histórico de backup completo de um banco de dados, use o seguinte script de exemplo:

SELECT bs.database_name,
    CASE
        WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN 'Full Database'
        WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
        WHEN bs.type = 'I' THEN 'Differential database backup'
        WHEN bs.type = 'L' THEN 'Transaction Log'
        WHEN bs.type = 'F' THEN 'File or filegroup'
        WHEN bs.type = 'G' THEN 'Differential file'
        WHEN bs.type = 'P' THEN 'Partial'
        WHEN bs.type = 'Q' THEN 'Differential partial'
    END + ' Backup' AS backuptype,
    bs.recovery_model,
    bs.Backup_Start_Date AS BackupStartDate,
    bs.Backup_Finish_Date AS BackupFinishDate,
    bf.physical_device_name AS LatestBackupLocation,
    bs.backup_size / 1024. / 1024. AS backup_size_mb,
    bs.compressed_backup_size / 1024. / 1024. AS compressed_backup_size_mb,
    database_backup_lsn, -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on.
    checkpoint_lsn,
    begins_log_chain
FROM msdb.dbo.backupset AS bs
     LEFT OUTER JOIN msdb.dbo.backupmediafamily AS bf
         ON bs.[media_set_id] = bf.[media_set_id]
WHERE recovery_model IN ('FULL', 'BULK-LOGGED')
      AND bs.backup_start_date > DATEADD(month, -2, SYSDATETIME()) --only look at last two months
ORDER BY bs.database_name ASC, bs.Backup_Start_Date DESC;

Um histórico completo de todas as operações de backup e restauração do SQL Server em uma instância de servidor é armazenado no banco de dados do msdb sistema. Para obter mais informações sobre o histórico de backup, consulte Histórico de backup e informações de cabeçalho (SQL Server).

Criar um backup de log de transações

Exemplo de como fazer backup do log:

BACKUP LOG [dbname] TO DISK = 'some_volume:\some_folder\dbname_LOG.trn';

Importante

Se o banco de dados estiver danificado, consulte os backups do tail-log (SQL Server).

Transação Ativa log_reuse_wait

As etapas para solucionar ACTIVE_TRANSACTION o motivo incluem descobrir a transação de longa duração e resolvê-la (em alguns casos, usando o KILL comando para fazê-lo).

Descubra transações de longa duração

Uma transação de longa duração pode fazer com que o log de transações seja preenchido. Para procurar transações de longa duração, use uma das seguintes opções:

  • sys.dm_tran_database_transactions:

    Essa exibição de gerenciamento dinâmico retorna informações sobre transações no nível do banco de dados. Para uma transação de longa duração, as colunas de interesse particular incluem a hora do primeiro registro de log (database_transaction_begin_time), o estado atual da transação (database_transaction_state) e o número de sequência de log (LSN) do BEGIN registro no log de transações ().database_transaction_begin_lsn

  • DBCC OPENTRAN:

    Esta instrução permite identificar o ID de usuário do proprietário da transação, para que você possa rastrear a origem da transação para uma rescisão mais ordenada (confirmando-a em vez de revertê-la).

Encerrar uma transação

Às vezes, você só tem que terminar a transação; talvez seja necessário usar a instrução KILL . Use a instrução KILL com extrema cautela, especialmente quando estão a decorrer processos críticos que não deseja terminar.

PONTO DE VERIFICAÇÃO log_reuse_wait

Nenhum ponto de verificação ocorreu desde o último truncamento de log, ou o cabeçalho do log ainda não foi movido além de um arquivo de log virtual (VLF), em todos os modelos de recuperação.

Este é um razão comum para atrasar o truncamento do log. Se houver atraso, considere executar o CHECKPOINT comando na base de dados ou examinar os registos de VLFs.

USE dbname;
CHECKPOINT;

SELECT * FROM sys.dm_db_log_info(db_id('dbname'));

Réplica_de_Disponibilidade espera_de_reutilização_de_log

Quando as alterações de transação na réplica principal do grupo de disponibilidade Always On ainda não estiverem confirmadas na réplica secundária, o log de transações da réplica primária não pode ser truncado. Isso pode fazer com que o log cresça e ocorre se a réplica secundária estiver definida para o modo de confirmação síncrona ou assíncrona. Para obter informações sobre como solucionar esse tipo de problema, consulte Erro 9002. O log de transações do banco de dados está cheio devido a AVAILABILITY_REPLICA erro.

Replicação, rastreio de alterações ou CDC

Recursos como replicação, controle de alterações e captura de dados de alteração (CDC) dependem do log de transações, portanto, se as transações ou alterações não forem entregues, isso pode impedir que o log de transações seja truncado.

Use DBCC OPENTRAN, Replication Monitor ou procedimentos armazenados para controle de alterações e CDC para investigar e resolver quaisquer problemas com esses recursos.

Encontre informações sobre os fatores de log_reuse_wait

Para obter mais informações, consulte Fatores que podem atrasar o truncamento de log.

2. Resolva o volume total do disco

Em algumas situações, o volume do disco que hospeda o arquivo de log de transações pode ser preenchido. Você pode realizar uma das seguintes ações para resolver a situação de registo cheio que resulta de um disco completo:

Espaço livre em disco

Talvez seja possível liberar espaço em disco na unidade de disco que contém o arquivo de log de transações do banco de dados excluindo ou movendo outros arquivos. O espaço em disco liberado permite que o sistema de recuperação aumente o arquivo de log automaticamente.

Mover o arquivo de log para um disco diferente

Se não conseguir libertar espaço em disco suficiente na unidade que contém atualmente o ficheiro de registo, considere mover o ficheiro para outra unidade com espaço suficiente.

Importante

Os arquivos de log nunca devem ser colocados em sistemas de arquivos compactados.

Consulte Mover arquivos de banco de dados para obter informações sobre como alterar o local de um arquivo de log.

Adicionar um arquivo de log em um disco diferente

Adicione um novo ficheiro de log à base de dados num disco diferente que tenha espaço suficiente usando ALTER DATABASE <database_name> ADD LOG FILE. Vários arquivos de log para um único banco de dados devem ser considerados uma condição temporária para resolver um problema de espaço, não uma condição de longo prazo. A maioria dos bancos de dados deve ter apenas um arquivo de log de transações. Continue a investigar o motivo pelo qual o log de transações está cheio e não pode ser truncado. Considere adicionar arquivos de log de transações temporários adicionais apenas como uma etapa avançada de solução de problemas.

Para obter mais informações, consulte Adicionar dados ou arquivos de log a um banco de dados.

Essas etapas podem ser parcialmente automatizadas executando o seguinte script T-SQL para identificar arquivos de log que usam uma grande porcentagem de espaço em disco e sugerir ações:

DECLARE @log_reached_disk_size AS BIT = 0;

SELECT [name] AS LogName,
       physical_name,
       CONVERT (BIGINT, size) * 8 / 1024 AS LogFile_Size_MB,
       volume_mount_point,
       available_bytes / 1024 / 1024 AS Available_Disk_space_MB,
       (CONVERT (BIGINT, size) * 8.0 / 1024) / (available_bytes / 1024 / 1024) * 100 AS file_size_as_percentage_of_disk_space,
       db_name(mf.database_id) AS DbName
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, file_id)
WHERE mf.[type_desc] = 'LOG'
    AND (CONVERT (BIGINT, size) * 8.0 / 1024) / (available_bytes / 1024 / 1024) * 100 > 90 --log is 90% of disk drive
ORDER BY size DESC;

IF @@ROWCOUNT > 0
    BEGIN
        SET @log_reached_disk_size = 1;
        -- Discover if any logs have filled the volume they reside on, or are close to filling the volume.
        -- Either add a new file to a new drive, or shrink an existing file.
        -- If it cannot shrink, direct the script to recommend next steps.
        DECLARE @db_name_filled_disk AS sysname, @log_name_filled_disk AS sysname, @go_beyond_size AS BIGINT;
        DECLARE log_filled_disk CURSOR
            FOR SELECT db_name(mf.database_id),
                       name
                FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, file_id)
                WHERE mf.[type_desc] = 'LOG'
                      AND (CONVERT (BIGINT, size) * 8.0 / 1024) / (available_bytes / 1024 / 1024) * 100 > 90 --log is 90% of disk drive
                ORDER BY size DESC;
        OPEN log_filled_disk;
        FETCH NEXT FROM log_filled_disk INTO @db_name_filled_disk, @log_name_filled_disk;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SELECT 'Transaction log for database "' + @db_name_filled_disk + '" has nearly or completely filled disk volume it resides on!' AS Finding;
                SELECT 'Consider using one of the below commands to shrink the "' + @log_name_filled_disk + '" transaction log file size or add a new file to a NEW volume' AS Recommendation;
                SELECT 'DBCC SHRINKFILE(''' + @log_name_filled_disk + ''')' AS Shrinkfile_Command;
                SELECT 'ALTER DATABASE ' + @db_name_filled_disk + ' ADD LOG FILE ( NAME = N''' + @log_name_filled_disk + '_new'', FILENAME = N''NEW_VOLUME_AND_FOLDER_LOCATION\' + @log_name_filled_disk + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' AS AddNewFile;
                SELECT 'If shrink does not reduce the file size, likely it is because it has not been truncated. Please review next section below. See https://learn.microsoft.com/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql' AS TruncateFirst;
                SELECT 'Can you free some disk space on this volume? If so, do this to allow for the log to continue growing when needed.' AS FreeDiskSpace;
                FETCH NEXT FROM log_filled_disk INTO @db_name_filled_disk, @log_name_filled_disk;
            END
        CLOSE log_filled_disk;
        DEALLOCATE log_filled_disk;
    END

3. Altere o limite de tamanho do log ou ative o crescimento automático

O erro 9002 pode ser gerado se o tamanho do log de transações estiver definido como um limite superior ou se o recurso de crescimento automático não for permitido. Nesse caso, habilitar o crescimento automático ou aumentar o tamanho do log manualmente pode ajudar a resolver o problema. Use este comando T-SQL para localizar esses arquivos de log e siga as recomendações fornecidas:

SELECT DB_NAME(database_id) AS DbName,
       name AS LogName,
       physical_name,
       type_desc,
       CONVERT (BIGINT, SIZE) * 8 / 1024 AS LogFile_Size_MB,
       CONVERT (BIGINT, max_size) * 8 / 1024 AS LogFile_MaxSize_MB,
       (SIZE * 8.0 / 1024) / (max_size * 8.0 / 1024) * 100 AS percent_full_of_max_size,
       CASE WHEN growth = 0 THEN 'AUTOGROW_DISABLED' ELSE 'Autogrow_Enabled' END AS AutoGrow
FROM sys.master_files
WHERE file_id = 2
      AND (SIZE * 8.0 / 1024) / (max_size * 8.0 / 1024) * 100 > 90
      AND max_size NOT IN (-1, 268435456)
      OR growth = 0;

IF @@ROWCOUNT > 0
    BEGIN
        DECLARE @db_name_max_size AS sysname, @log_name_max_size AS sysname, @configured_max_log_boundary AS BIGINT, @auto_grow AS INT;
        DECLARE reached_max_size CURSOR
        FOR SELECT db_name(database_id),
                    name,
                    CONVERT (BIGINT, SIZE) * 8 / 1024,
                    growth
            FROM sys.master_files
            WHERE file_id = 2
                AND ((SIZE * 8.0 / 1024) / (max_size * 8.0 / 1024) * 100 > 90
                    AND max_size NOT IN (-1, 268435456)
                    OR growth = 0);
        OPEN reached_max_size;
        FETCH NEXT FROM reached_max_size INTO @db_name_max_size, @log_name_max_size, @configured_max_log_boundary, @auto_grow;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @auto_grow = 0
                BEGIN
                    SELECT 'The database "' + @db_name_max_size + '" contains a log file "' + @log_name_max_size + '" whose autogrow has been DISABLED' AS Finding;
                    SELECT 'Consider enabling autogrow or increasing file size via these ALTER DATABASE commands' AS Recommendation;
                    SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', FILEGROWTH = 65536KB)' AS AutoGrowth;
                END
            ELSE
                BEGIN
                    SELECT 'The database "' + @db_name_max_size + '" contains a log file "' + @log_name_max_size + '" whose max limit is set to ' + CONVERT (VARCHAR (24), @configured_max_log_boundary) + ' MB and this limit has been reached!' AS Finding;
                    SELECT 'Consider using one of the below ALTER DATABASE commands to either change the log file size or add a new file' AS Recommendation;
                END
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = UNLIMITED)' AS UnlimitedSize;
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = something_larger_than_' + CONVERT (VARCHAR (24), @configured_max_log_boundary) + 'MB )' AS IncreasedSize;
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' ADD LOG FILE ( NAME = N''' + @log_name_max_size + '_new'', FILENAME = N''SOME_FOLDER_LOCATION\' + @log_name_max_size + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' AS AddNewFile;
            FETCH NEXT FROM reached_max_size INTO @db_name_max_size, @log_name_max_size, @configured_max_log_boundary, @auto_grow;
        END
        CLOSE reached_max_size;
        DEALLOCATE reached_max_size;
    END
ELSE
    SELECT 'Found no files that have reached max log file size' AS Findings;

Aumente o tamanho do arquivo de log ou habilite o crescimento automático

Se houver espaço disponível no disco de log, você poderá aumentar o tamanho do arquivo de log. O tamanho máximo dos ficheiros de registo é de 2 terabytes (TB) por ficheiro de registo.

Se o crescimento automático estiver desativado, o banco de dados estiver online e houver espaço suficiente disponível no disco, considere seguir as seguintes etapas:

Observação

Em ambos os casos, se o limite de tamanho atual for atingido, aumente o MAXSIZE valor.

Conteúdo relacionado

  • Last updated on