Устранение неполадок с полным журналом транзакций (ошибка 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 (Transact-SQL). Описание причин, по которым может задержаться усечение журнала, см. в статье Журнал транзакций (SQL Server).

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

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 defferred 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://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 ''' + @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 encoutered 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://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 [' +@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

Важно!

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

LOG_BACKUP log_reuse_wait

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

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

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

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

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

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

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;

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

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

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

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

Важно!

Если база данных повреждена, см. статью "Резервные копии tail-Log" (SQL Server).

ACTIVE_TRANSACTION log_reuse_wait

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

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

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

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

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

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

AVAILABILITY_REPLICA log_reuse_wait

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

CHECKPOINT log_reuse_wait

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

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

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

Дополнительные сведения о факторах log_reuse_wait

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

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

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

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

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

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

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

Важно!

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

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

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

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

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

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

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://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) 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

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

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

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

Примечание.

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

Следующие шаги