Поделиться через

Устранение неполадок с полным журналом транзакций (ошибка SQL Server 9002)

  • Статья

Применимо к:SQL Server

Эта статья относится к экземплярам SQL Server.

Примечание.

Эта статья посвящена SQL Server. Дополнительные сведения об этой ошибке на платформах SQL Azure см. в статье "Устранение ошибок журнала транзакций с помощью базы данных SQL Azure и устранение ошибок журнала транзакцийс помощью Управляемого экземпляра SQL Azure". База данных SQL Azure и Управляемый экземпляр SQL Azure основаны на последней стабильной версии ядра СУБД Microsoft SQL Server, поэтому большая часть содержимого аналогична, хотя варианты устранения неполадок и средства могут отличаться.

Вариант 1. Выполнение шагов непосредственно в исполняемой записной книжке с помощью Azure Data Studio

Прежде чем пытаться открыть эту записную книжку, убедитесь, что на локальном компьютере установлен экземпляр Azure Data Studio. Чтобы установить, перейдите к разделу "Скачать и установить Azure Data Studio".

Открытие записной книжки в Azure Data Studio

Вариант 2. Выполните действия вручную.

В этой статье рассматриваются возможные ответы на полный журнал транзакций и предполагается, как избежать его в будущем.

Когда журнал транзакций станет полным, ядро СУБД SQL Server выдает ошибку 9002. Журнал может заполниться, когда база данных работает в режиме "в сети" или находится в процессе восстановления. Если журнал переполняется при подключенной базе данных, она не отключается, но переходит в режим только для чтения. Если журнал заполняется во время восстановления, ядро СУБД помечает базу данных как RESOURCE PENDING. В любом случае необходимо вмешательство пользователя, чтобы сделать журнал транзакций доступным.

Распространенные причины переполнения журнала транзакций

Предпринимаемые действия при переполнении журнала транзакций зависят от условий, которые вызвали эту ситуацию. Наиболее вероятные причины:

  • журнал не усекается;
  • Том диска заполнен
  • для размера журнала задано фиксированное максимальное значение или отключено автоматическое увеличение;
  • не удается завершить синхронизацию группы доступности или репликацию.

Выполните указанные ниже действия, чтобы узнать причину полного журнала транзакций и устранить проблему.

1. Усечение лог-файла

Распространенное решение этой проблемы заключается в том, чтобы обеспечить выполнение резервных копий журналов транзакций для базы данных, что гарантирует усечение журнала. Если для базы данных с полным журналом транзакций не указано ни одного журнала транзакций, решить эту проблему будет просто. Достаточно возобновить создание регулярных резервных копий журналов транзакций базы данных.

Дополнительные сведения см. в статье "Управление размером файла журнала транзакций " и "Сжатие файла".

Описание процесса усечения журнала

Существует разница между усечением журнала транзакций и сжатием журнала транзакций. Усечение журнала обычно происходит во время резервного копирования журнала транзакций и является логической операцией, которая удаляет зафиксированные записи внутри журнала, в то время как сжатие журнала освобождает физическое пространство в файловой системе, уменьшая размер файла. Усечение журнала происходит на границе виртуального журнального файла (VLF), а файл журнала может содержать множество VLF. Файл журнала может быть сжат только в том случае, если в файле журнала пустое место для восстановления. Сжатие файла журнала не может решить проблему полного файла журнала. Вместо этого необходимо выяснить, почему файл журнала заполнен и не может быть усечен.

Предупреждение

Данные, перемещаемые в процессе сжатия файла, могут быть разбросаны по любым доступным местам в файле. Это вызывает фрагментацию индекса и может замедлить выполнение запросов, при котором осуществляется поиск в диапазоне индекса. Чтобы устранить фрагментацию, предусмотрите возможность перестроения индексов файла после сжатия. Дополнительные сведения см. в разделе "Сжатие базы данных".

Что препятствует усечению журнала?

Чтобы определить, что препятствует усечению журнала в определенном случае, используйте столбцы log_reuse_wait и log_reuse_wait_desc представления каталога sys.databases. Дополнительные сведения см. в статье sys.databases. Чтобы узнать об описаниях факторов, которые могут задержать усечение журнала, см. Журнал транзакций.

Следующий набор команд T-SQL помогает определить, не усекается ли журнал транзакций базы данных и причина. В следующем скрипте также рекомендуется выполнить действия по устранению проблемы:

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 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 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;

Внимание

Если база данных была восстановлена, когда произошла ошибка 9002, после устранения проблемы восстановите базу данных с помощью ALTER DATABASE database_name SET ONLINE.

LOG_BACKUP log_reuse_wait

Наиболее распространенное действие, которое следует предпринять, если вы видите LOG_BACKUP или log_reuse_wait, — это пересмотреть модель восстановления базы данных и создать резервную копию журнала транзакций вашей базы данных.

Рассмотрим модель восстановления базы данных

Журнал транзакций может не усекаться с категорией LOG_BACKUP или log_reuse_wait, потому что вы никогда не делали его резервную копию. Во многих из этих случаев ваша база данных использует модель восстановления FULL или BULK_LOGGED, но вы не сделали резервную копию журнала транзакций. Следует тщательно рассмотреть каждую модель восстановления базы данных: выполнять регулярные резервные копии журналов транзакций во всех базах данных или FULLBULK_LOGGED моделях восстановления, чтобы свести к минимуму случаи ошибки 9002. Дополнительные сведения см. в разделе "Модели восстановления".

Резервное копирование журнала

Под FULL или BULK_LOGGED моделью восстановления, если журнал транзакций недавно не был архивирован, резервное копирование может быть тем, что предотвращает обрезку журнала. Необходимо создать резервную копию журнала транзакций, чтобы разрешить освобождение записей журнала и усечение журнала. Если журнал никогда не был резервирован, необходимо создать две резервные копии журналов , чтобы разрешить ядро СУБД усечь журнал до точки последней резервной копии. Усечение журнала освобождает логическое пространство для новых записей журнала. Чтобы избежать повторного переполнения журнала, резервное копирование следует выполнять часто и регулярно. Дополнительные сведения см. в разделе "Модели восстановления".

Полный журнал резервных копий и операций восстановления в экземпляре сервера SQL Server хранится в системной базе данных msdb. Чтобы ознакомиться с полным журналом резервного копирования базы данных, используйте следующий пример скрипта:

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;

Полный журнал резервных копий и операций восстановления в экземпляре сервера SQL Server хранится в системной базе данных msdb. Для получения дополнительной информации об истории резервного копирования см. в разделе "История резервного копирования и информация о заголовке" (SQL Server).

Создание резервной копии журналов транзакций

Пример создания резервной копии журнала:

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

Внимание

Если база данных повреждена, ознакомьтесь с резервными копиями журналов (SQL Server).

АКТИВНАЯ_ТРАНЗАКЦИЯ log_reuse_wait

Действия по устранению неполадок ACTIVE_TRANSACTION включают обнаружение длительной транзакции и ее разрешение (в некоторых случаях с помощью команды KILL для этого).

Обнаружение длительных транзакций

Длинная транзакция может привести к заполнению журнала транзакций. Чтобы искать длительные транзакции, используйте один из следующих вариантов:

  • sys.dm_tran_database_transactions:

    Данное динамическое административное представление возвращает сведения о транзакциях на уровне базы данных. Для длительной транзакции столбцы определенного интереса включают время первой записи журнала (database_transaction_begin_time), текущее состояние транзакции (database_transaction_state) и номер последовательности журнала (LSN) для записи в журнале транзакций (BEGINdatabase_transaction_begin_lsn).

  • DBCC OPENTRAN:

    Эта инструкция позволяет установить идентификатор владельца транзакции, таким образом, можно отследить источник транзакции для более упорядоченной остановки (фиксацией, а не откатом).

Завершение транзакции

Иногда нужно просто завершить транзакцию; Может потребоваться использовать инструкцию KILL . Используйте инструкцию KILL с крайней осторожностью, особенно если выполняются критически важные процессы, которые вы не хотите прерывать.

log_reuse_wait КОНТРОЛЬНАЯ ТОЧКА

С момента последнего усечения журнала не была создана контрольная точка. Или же голова журнала еще не переместилась за пределы виртуального файла журнала (VLF) во всех моделях восстановления.

Это широко распространенная причина задержки усечения журнала. При задержке рассмотрите возможность выполнения CHECKPOINT команды в базе данных или проверки VLFs журнала.

USE dbname;
CHECKPOINT;

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

AVAILABILITY_REPLICA log_reuse_wait

Если изменения транзакций в первичной реплике группы доступности AlwaysOn еще не затверждены на вторичной реплике, журнал транзакций первичной реплики не может быть усечен. Это может привести к росту журнала и возникает ли вторичная реплика для синхронного или асинхронного режима фиксации. Сведения об устранении этой проблемы см. в статье Об ошибке 9002. Журнал транзакций для базы данных заполнен из-за ошибки AVAILABILITY_REPLICA.

Репликация, отслеживание изменений или CDC

Такие функции, как репликация, отслеживание изменений и запись измененных данных (CDC), зависят от журнала транзакций, поэтому, если транзакции или изменения не доставлены, это может предотвратить усечение журнала транзакций.

Используйте DBCC OPENTRAN, монитор репликации или хранимые процедуры для отслеживания изменений и CDC для изучения и устранения проблем с этими функциями.

Поиск сведений о факторах log_reuse_wait

Дополнительные сведения см. в разделе "Факторы, которые могут отложить усечение журнала".

2. Устранение проблемы с переполнением тома диска

В некоторых ситуациях том диска, на котором размещен файл журнала транзакций, может заполниться. Вы можете выполнить одно из следующих действий, чтобы решить проблему с переполнением журнала, которая приводит к заполнению диска:

Объем свободного места на диске

Возможно, следует освободить место на диске, где находится файл журнала транзакций для базы данных. Для этого можно удалить или переместить другие файлы. Освобожденное место на диске позволит системе восстановления автоматически увеличить размер файла журнала.

Перемещение файла журнала на другой диск

Если вы не можете освободить достаточно места на диске, который в настоящее время содержит файл журнала, рассмотрите возможность перемещения файла на другой диск с достаточным пространством.

Внимание

Файлы журнала ни в коем случае не следует размещать в файловых системах со сжатием.

Сведения о том, как изменить расположение файла журнала, см. в разделе "Перемещение файлов базы данных ".

Добавление файла журнала на другой диск

Добавьте новый файл журнала в базу данных на другом диске, где достаточно места, с помощью ALTER DATABASE <database_name> ADD LOG FILE. Использование нескольких файлов журналов для одной базы данных может быть временным решением проблемы с пространством. В большинстве баз данных должен быть только один файл журнала транзакций. Продолжайте исследовать причину, по которой журнал транзакций заполнен и не может быть усечен. Рассмотрите возможность добавления дополнительных временных файлов журнала транзакций только в качестве шага по устранению неполадок.

Дополнительные сведения см. в разделе "Добавление данных или файлов журналов" в базу данных.

Эти действия можно частично автоматизировать, выполнив следующий скрипт T-SQL, чтобы определить файлы журналов, использующие большой процент дискового пространства и предложив действия:

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. Изменение ограничения размера журнала или включение автоматического увеличения

Ошибка 9002 может быть создана, если размер журнала транзакций имеет верхний предел или функция автоматического увеличения не разрешена. В этом случае проблему, возможно, поможет решить включение автоматического увеличения или увеличение размера журнала вручную. Используйте эту команду T-SQL для поиска таких файлов журнала и следуйте приведенным ниже рекомендациям.

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;

Увеличьте размер файла журнала или включите автоматическое увеличение

Если на диске, на котором находится журнал, доступно свободное место, можно увеличить размер файла журнала. Максимальный размер файлов журнала составляет 2 терабайта (ТБ) для каждого файла журнала.

Если автоматическое увеличение отключено, база данных находится в сети и достаточно места на диске, рассмотрите следующие действия.

Примечание.

В любом случае, если достигнуто текущее ограничение размера, увеличьте MAXSIZE значение.

Связанный контент