Solução de problemas em um log de transação completa (SQL Server Erro 9002)

Aplica-se: SQL Server

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

Opção 1: executar as etapas diretamente em um notebook executável usando o Azure Data Studio

Opção 2: seguir a etapa manualmente

Este artigo aborda as respostas possíveis a um log de transações completo e sugere como evitar isso 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 ficar completo quando o banco de dados estiver online ou em recuperação. Se o log ficar cheio enquanto o banco de dados estiver online, o banco de dados permanecerá online, mas só poderá ser lido, não atualizado. Se o log ficar completo durante uma recuperação, o Mecanismo de Banco de Dados marcará o banco de dados como RESOURCE PENDING. Em qualquer caso, é necessária a ação do usuário para liberar espaço no log.

Motivos comuns para o log de transações ficar cheio

A resposta apropriada quando o log de transações fica cheio depende das condições que causaram isso. As causas mais comuns incluem:

• O log não está sendo truncado
• O volume de disco está cheio
• O tamanho do log está definido como um valor máximo fixo ou o crescimento automático está desativado
• Falha ao realizar a replicação ou a sincronização do grupo de disponibilidade

Como resolver o log de transações cheio

As etapas específicas abaixo ajudarão você a encontrar o motivo para o log de transações estar cheio e resolver o problema.

1. Truncar o log

Uma solução muito comum para esse problema é garantir que os backups de log de transações sejam executados em seu banco de dados, o que garantirá que o log seja truncado. Quando o banco de dados com o log de transações cheio não tem um histórico do log, a solução para o problema é simples: retome os backups regulares do log de transações do banco de dados.

Para obter mais informações, revise Gerenciar tamanho do log e Reduzir um arquivo.

Truncamento de log explicado

Há uma diferença entre truncar e reduzir o log de transações. O truncamento de log ocorre normalmente durante o 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 o espaço físico no sistema de arquivos, reduzindo o tamanho do arquivo. O truncamento de log ocorre em um limite de VLF (arquivo de log virtual), e um arquivo de log pode conter muitos VLFs. Somente é possível reduzir o arquivo de log quando ele contém espaço vazio a ser recuperado. Reduzir um arquivo de log sozinho não consegue 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.

O que está impedindo o truncamento de log?

Para descobrir o que está impedindo o truncamento de log em um determinado caso, use as colunas log_reuse_wait e log_reuse_wait_desc da exibição de catálogo sys.databases. Para obter mais informações, confira sys.databases (Transact-SQL). Para descrições de fatores que podem adiar o truncamento de log, confira O log de transações (SQL Server).

O conjunto de comandos T-SQL abaixo ajuda a identificar se um log de transações do 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 VARCHAR (8000), @log_reuse_wait tinyint, @log_reuse_wait_desc nvarchar(120), @dbname sysname, @database_id int, @recovery_model_desc 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 ' + @dbname +'.sys.dm_db_log_space_usage'

    BEGIN TRY
        exec (@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,
    log_reuse_wait_explanation = 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,

    sdb.database_id,
    sdb.recovery_model_desc,
    lsu.used_log_space_in_bytes / 1024 as Used_log_size_MB,
    lsu.total_log_size_in_bytes / 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 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)
        select '-- ''' + @dbname +  ''' database has log_reuse_wait = ' + @log_reuse_wait_desc + ' --'  as 'Individual Database Report'

    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 ''' + @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 ''' + @dbname+ ''' database? Review recovery models and determine if you need to change it. https://zcusa.951200.xyz/sql/relational-databases/backup-restore/recovery-models-sql-server' as RecoveryModelChoice
        select 'To truncate the log consider performing a transaction log backup on database ''' + @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 [' + @dbname + '] TO DISK = ''some_volume:\some_folder\' + @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 ''' +@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 ''' +@dbname+ '''. To check for active transactions, run these commands:' as Recommendation
        select 'DBCC OPENTRAN (''' +@dbname+ ''')' as FindOpenTran
        select 'select database_id, db_name(database_id) 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 ''' +@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 ''' +@dbname+ ''' may be behind: check unsent_log, send_rate, unrestored_log, recovery_rate, average_delay in this output' as Recommendation
        select 'exec msdb.sys.sp_dbmmonitoraddmonitoring 1; exec msdb.sys.sp_dbmmonitorresults ''' + @dbname+ ''', 5, 0; waitfor delay ''00:01:01''; exec msdb.sys.sp_dbmmonitorresults ''' + @dbname+ '''; exec msdb.sys.sp_dbmmonitordropmonitoring'   as CheckMirroringStatusAnd
    END

    else if (@log_reuse_wait = 6)
    BEGIN
        select 'Replication transactions still undelivered from publisher database ''' +@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  (''' + @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 ''' +@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://zcusa.951200.xyz/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 [' +@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://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/' as ReviewBlog
        select 'use ' +@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

LOG_BACKUP log_reuse_wait

A ação mais comum que você pode considerar se ver LOG_BACKUP ou log_reuse_wait são revisar seu modelo de recuperação de 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 a categoria LOG_BACKUP log_reuse_wait, porque você nunca fez backup dele. Em muitos desses casos, seu banco de dados está usando o modelo de recuperação FULL ou BULK_LOGGED, mas você não fez o backup de logs de transações. Você deve considerar cada modelo de recuperação do banco de dados com cuidado: execute regularmente backups 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 do erro 9002. Para saber mais, confira Modelos de recuperação.

Fazer backup do log

No modelo de recuperação FULL ou BULK_LOGGED, se não foi feito backup do log de transações recentemente, pode ser que o backup esteja impedindo o truncamento de log. Você deve fazer o backup do log de transações para permitir que os registros de log sejam liberados e o log seja truncado. Se nunca foi feito backup do log, é necessário criar dois backups de log para permitir que o Mecanismo de Banco de Dados trunque o log no ponto do último backup. O truncamento do log libera espaço lógico para novos registros de log. Para impedir que o log fique cheio novamente, faça backups de log regulares e mais frequentes. Para saber mais, confira 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 do servidor é armazenado no banco de dados do sistema msdb. Para examinar o histórico de backup completo de um banco de dados, use o seguinte script de exemplo:

SELECT bs.database_name
, backuptype = 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'
, bs.recovery_model
, BackupStartDate = bs.Backup_Start_Date
, BackupFinishDate = bs.Backup_Finish_Date
, LatestBackupLocation = bf.physical_device_name
, backup_size_mb = bs.backup_size/1024./1024.
, compressed_backup_size_mb = bs.compressed_backup_size/1024./1024.
, 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 do servidor é armazenado no banco de dados do sistema msdb. Para obter informações sobre essas tabelas, confira Histórico de backup e informações do cabeçalho (SQL Server).

Criar um backup de log de transações

Exemplo de como fazer o backup do log:

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

• Fazer backup de um log de transações (SQL Server)

• SqlBackup (SMO)

ACTIVE_TRANSACTION log_reuse_wait

As etapas para solucionar problemas do motivo ACTIVE_TRANSACTION incluem descobrir a transação de execução prolongada e resolvê-la (em alguns casos, usando o comando KILL para fazer isso).

Descobrindo transações de execução prolongada

Uma transação muito demorada pode fazer com que o log de transações fique cheio. Para procurar transações demoradas, use um dos seguintes:

• sys.dm_tran_database_transactions.

Essa exibição de gerenciamento dinâmico retorna informações sobre as transações no banco de dados. Para uma transação demorada, as colunas de interesse específico 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 registro de INÍCIO do log de transações (database_transaction_begin_lsn).

• DBCC OPENTRAN. Essa instrução permite identificar a ID do proprietário da transação, de modo que seja possível localizar potencialmente a origem da transação para um término mais ordenado (confirmar, em vez de revertê-la).

Encerrar uma transação

Às vezes, você precisa encerrar a transação. Talvez seja necessário usar a instrução KILL. Use a instrução KILL com muito cuidado, especialmente quando processos críticos que você não deseja encerrar estiverem em execução.

CHECKPOINT log_reuse_wait

Não ocorreu nenhum ponto de verificação desde o último truncamento de log ou a parte inicial do log ainda não foi passou além de um VLF (arquivo de log virtual). (Todos os modelos de recuperação)

Essa é uma razão rotineira para atrasar o truncamento de log. Se estiver atrasado, considere executar o comando CHECKPOINT no banco de dados ou examinar o log VLFs.

USE dbname; CHECKPOINT
select * from sys.dm_db_log_info(db_id('dbname'))

AVAILABILITY_REPLICA log_reuse_wait

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

Replicação, controle 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, Monitor de replicação ou procedimentos armazenados para controle de alterações e CDC para investigar e resolver quaisquer problemas com esses recursos.

Para obter mais informações sobre fatores do log_reuse_wait

Para obter detalhes, confira Fatores que podem atrasar o truncamento de log.

2. Resolver o volume de disco cheio

Em algumas situações, o volume de disco que hospeda o arquivo de log de transações fica cheio. Você pode realizar uma das seguintes ações para resolver o problema de log cheio causado pelo disco cheio:

Espaço livre em disco

Pode ser possível liberar espaço de 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 de 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 você não puder liberar espaço em disco suficiente na unidade que contém o arquivo de log, pense em mover o arquivo para outra unidade com espaço suficiente.

Confira Mover arquivos de banco de dados para ver como alterar o local de um arquivo de log.

Adicionar um arquivo de log a um disco diferente

Para adicionar um novo arquivo de log ao banco de dados em um disco diferente que tenha espaço suficiente, use ALTER DATABASE <database_name> ADD LOG FILE. Ter vários arquivos de log para um banco de dados individual deve ser considerado uma condição temporária para resolver um problema de espaço, e 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 mais arquivos de log de transações temporários apenas como etapa avançada de solução de problemas.

Para obter mais informações, consulte adicionar dados ou arquivos de Log para um banco de dados.

Script utilitário para ações recomendadas

É possível automatizar parcialmente essas etapas com o seguinte script T-SQL, para identificar os arquivos de log que usam uma grande porcentagem do espaço em disco e sugerir ações:

DECLARE @log_reached_disk_size BIT = 0

SELECT
    name LogName,
    physical_name,
    CONVERT(bigint, size)*8/1024 LogFile_Size_MB,
    volume_mount_point,
    available_bytes/1024/1024 Available_Disk_space_MB,
    (CONVERT(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 file_size_as_percentage_of_disk_space,
    db_name(mf.database_id) DbName
FROM sys.master_files 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 are close to or completely filled disk volume they reside on.
    -- Either Add A New File To A New Drive, Or Shrink Existing File
    -- If Cannot Shrink, Go To Cannot Truncate Section

    DECLARE @db_name_filled_disk sysname, @log_name_filled_disk sysname, @go_beyond_size bigint

    DECLARE log_filled_disk CURSOR FOR
        SELECT
            db_name(mf.database_id),
            name
        FROM sys.master_files 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://zcusa.951200.xyz/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. Alterar o limite de tamanho do log ou habilitar o crescimento automático

O erro 9002 poderá ocorrer se o tamanho do log de transações for 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 encontrar esses arquivos de log e siga as recomendações exibidas:

SELECT DB_NAME(database_id) DbName,
       name LogName,
       physical_name,
       type_desc ,
       CONVERT(bigint, SIZE)*8/1024 LogFile_Size_MB ,
       CONVERT(bigint,max_size)*8/1024 LogFile_MaxSize_MB ,
       (SIZE*8.0/1024)/(max_size*8.0/1024)*100 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 sysname, @log_name_max_size sysname, @configured_max_log_boundary bigint, @auto_grow 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

Aumentar o tamanho do arquivo de log ou habilitar o crescimento automático

Se houver espaço disponível no disco de log, você pode aumentar o tamanho do arquivo de log. O tamanho máximo para arquivos de log é de dois terabytes (TB) por arquivo de log.

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

• Aumentar manualmente o tamanho de arquivo para produzir um único incremento de crescimento. Essas são recomendações gerais sobreo tamanho e o crescimento do log.
• Ativar o crescimento automático usando a instrução ALTER DATABASE para definir um incremento de crescimento diferente de zero para a opção FILEGROWTH. Confira Considerações sobre as configurações de crescimento automático e redução automática no SQL Server.

Conteúdo relacionado

• ALTER DATABASE (Transact-SQL)
• Gerenciar o tamanho do arquivo de log de transações
• Backups de log de transações (SQL Server)
• sp_add_log_file_recover_suspect_db (Transact-SQL)
• MSSQLSERVER_9002
• Como uma estrutura do arquivo de log pode afetar o tempo de recuperação do banco de dados – Microsoft Tech Community