満杯になったトランザクションログのトラブルシューティング (SQL Server エラー 9002)

[アーティクル]
08/30/2023

適用対象:SQL Server

この記事の対象は、SQL Server インスタンスです。

Note

この記事では、SQL Server について重点的に説明します。 Azure SQL プラットフォームでのこのエラーの詳細については、「Azure SQL Database のトランザクションログエラーのトラブルシューティング」および「Azure SQL Managed Instance のトランザクションログエラーのトラブルシューティング」を参照してください。 Azure SQL Database と Azure SQL Managed Instance は、Microsoft SQL Server データベースエンジンの最新の安定バージョンに基づいているため、トラブルシューティングのオプションやツールに違いがある場合があるものに、コンテンツの多くは似ています。

オプション 1: Azure Data Studio から実行可能なノートブックで直接ステップを実行する

Note

このノートブックを開く前に、Azure Data Studio がローカルコンピューターにインストールされていることを確認してください。インストールするには、Azure Data Studio のインストール方法に関するページを参照してください。

Azure Data Studio でノートブックを開く

オプション 2: 手動で手順を実行する

この記事では、トランザクションログが満杯になった場合の対処法について説明し、今後トランザクションログが満杯になるのを防ぐ方法を示します。

トランザクションログがいっぱいになったら、SQL Server データベースエンジンによって 9002 エラーが発行されます。データベースがオンラインまたは復旧中の場合、ログが満杯になることがあります。データベースがオンラインのときにログファイルがいっぱいになった場合、データベースはオンラインのままです。ただし、更新はできず、読み取りだけが可能です。復旧中にログが満杯になった場合、データベースエンジンによりデータベースが RESOURCE PENDING としてマークされます。いずれの場合も、ログ領域を使用可能にするためのユーザー操作が必要です。

トランザクションログが満杯になる一般的な理由

トランザクションログが満杯になった状況によって、適切な対処法が異なる場合があります。一般的な原因には、次のようなものがあります。

ログが切り詰められていない
ディスクボリュームが満杯
ログサイズが固定最大値に設定されている、または自動拡張が無効
完了できないレプリケーションまたは可用性グループの同期

トランザクションログが満杯になった場合の解決方法

次の具体的な手順は、トランザクションログが満杯になる理由を見つけて問題を解決するのに役立ちます。

1. ログを切り詰める

この問題に対するごく一般的な解決策は、データベースに対してトランザクションログバックアップが実行されるようにすることです。これによりログは切り詰めるられるようになります。満杯のトランザクションログを持つデータベースについて最近のトランザクションログ履歴が示されていない場合、問題の解決策は簡単です。データベースの通常のトランザクションログバックアップを再開します。

ログの切り詰めについての説明

トランザクションログの切り詰めとトランザクションログの圧縮には、違いがあります。ログの切り詰めは通常、トランザクションログのバックアップ中に行われます。これは、ログ内のコミットされたレコードを削除する論理操作です。一方、ログの圧縮では、ファイルサイズを小さくすることで、ファイルシステム上の物理領域が再利用されます。ログの切り詰めは仮想ログファイル (VLF) 境界で行われます。ログファイルには多数の VLF が含まれる場合があります。ログファイルの圧縮は、再利用するログファイル内に空の領域がある場合にのみ可能です。ログファイルを圧縮するだけでは、ログファイルが満杯になる問題を解決できません。代わりに、ログファイルが満杯で、切り詰めできない理由を見つける必要があります。

警告

ファイルを圧縮するために移動されたデータは、ファイル内のあらゆる使用可能な場所に分散される場合があります。これにより、インデックスの断片化が発生し、広範なインデックスを検索するクエリのパフォーマンスが低下する場合があります。断片化を解消するには、圧縮後にファイルのインデックスを再構築することを検討してください。詳細については、「データベースの圧縮」を参照してください。

何がログの切り詰めを妨げているか

特定のケースでログの切り捨てが妨げられる原因を見つけるには、sys.databases カタログビューの log_reuse_wait および log_reuse_wait_desc 列を使用します。詳細については、「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 database_name SET ONLINE ステートメントを使用してデータベースを復旧します。

LOG_BACKUP log_reuse_wait

ここで検討できる最も一般的なアクションは、データベース復旧モデルを確認し、データベースのトランザクションログをバックアップすることです。

データベースの復旧モデルを検討する

トランザクションログは、バックアップしたことがないため、LOG_BACKUP log_reuse_wait カテゴリでの切り詰めに失敗する可能性があります。このようなケースの多くでは、データベースで FULL または BULK_LOGGED 復旧モデルを使用していますが、トランザクションログをバックアップしていません。各データベースの復旧モデルを慎重に検討する必要があります。エラー 9002 の発生を最小限に抑えるために、すべてのデータベースに対して FULL または BULK LOGGED 復旧モデルでトランザクションログバックアップを実行します。詳しくは、「復旧モデル」をご覧ください。

ログをバックアップする

FULL または BULK_LOGGED 復旧モデルでは、トランザクションログを長期間バックアップしていないと、バックアップによりログの切り詰めが妨げられる場合があります。トランザクションログをバックアップして、ログレコードの解放とログの切り詰めを可能にする必要があります。これまでにログのバックアップをまったく行っていない場合は、ログのバックアップを 2 つ作成して、データベースエンジンが最後にバックアップされた時点までログを切り捨てられるようにする必要があります。ログを切り詰めると、新しいログレコード用に論理領域が解放されます。ログが再び満杯にならないようにするには、ログを定期的かつ頻繁にバックアップするようにしてください。詳しくは、「復旧モデル」をご覧ください。

サーバーインスタンスで行われたすべての 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'

重要

データベースが破損している場合は、ログ末尾のバックアップ (SQL Server) に関する記事を参照してください。

ACTIVE_TRANSACTION log_reuse_wait

ACTIVE_TRANSACTION の原因をトラブルシューティングする手順には、実行時間の長いトランザクションの検出とその解決が含まれます (場合によっては、そのために KILL コマンドを使用します)。

実行時間の長いトランザクションの検出

実行時間の非常に長いトランザクションがあると、トランザクションログがいっぱいになる可能性があります。実行時間の長いトランザクションを検索するには、以下のいずれかの方法を使用します。

sys.dm_tran_database_transactions。

この動的管理ビューは、データベースレベルでのトランザクションに関する情報を返します。実行時間の長いトランザクションで特に関係のある列としては、最初のログレコードの時間 (database_transaction_begin_time)、トランザクションの現在の状態 (database_transaction_state)、トランザクションログ内の開始レコードのログシーケンス番号 (LSN)(database_transaction_begin_lsn)があります。

DBCC OPENTRAN。 このステートメントを使用すると、トランザクション所有者のユーザー ID を特定できます。これにより、トランザクションの実行元を特定して、より規則正しくトランザクションを終了する (トランザクションをロールバックするのではなくコミットする) ことができます。

トランザクションの強制終了

状況によっては、単にトランザクションを終了する必要があります。このような場合、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 の要因に関する詳細情報

詳細については、「トランザクションログ (SQL Server)」を参照してください

2. 満杯のディスクボリュームを解決する

場合によっては、トランザクションログファイルをホストするディスクボリュームが満杯になることがあります。次のいずれかのアクションを実行して、ディスクが満杯の場合に発生するログ満杯シナリオを解決できます。

ディスクの空き領域

他のファイルを削除または移動することによって、データベースのトランザクションログファイルが保存されているディスクドライブのディスク領域を解放できる場合があります。ディスク領域を解放すると、ログファイルは自動的に拡張します。

別のディスクへのログファイルの移動

現在ログファイルが保存されているドライブでディスク領域を十分に解放できない場合は、十分な空き領域がある別のドライブにファイルを移動することを検討してください。

重要

ログファイルは、圧縮されたファイルシステムには配置しないでください。

ログファイルの場所を変更する方法については、「データベースファイルの移動」を参照してください。

別のディスクへのログファイルの追加

ALTER DATABASE <database_name> ADD LOG FILE を使用して、十分な領域を持つ別のディスク上のデータベースに新しいログファイルを追加します。 1 つのデータベースの複数のログファイルは、スペースの問題を解決するための長期的な条件ではなく、一時的な条件と見なす必要があります。ほとんどのデータベースには、トランザクションログファイルが 1 つだけあるはずです。トランザクションログが満杯で切り詰めできない理由を引き続き調査してください。高度なトラブルシューティング手順として、一時的に付加的なトランザクションログファイルを追加することを検討してください。

詳細については、「データベースに対するデータファイルまたはログファイルの追加」を参照してください。

推奨されるアクションのためのユーティリティスクリプト

これらの手順の一部を自動化するには、この 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 テラバイト (TB) です。

自動拡張が無効に設定されており、データベースがオンラインで、ディスク上に十分な空き領域がある場合、次のいずれかを実行します。

ファイルサイズを手動で増やし、単一の拡張増分値を作成します。ログサイズの増加とサイズに関する一般的な推奨事項があります。
ALTER DATABASE ステートメントを使用して自動拡張を有効にし、FILEGROWTH オプションに 0 以外の拡張増分値を設定します。「SQL Server での自動拡張と自動圧縮の設定に関する考慮事項」を参照してください

Note

いずれの場合も、現在のサイズ制限に達している場合は、MAXSIZE 値を増やします。

満杯になったトランザクションログのトラブルシューティング (SQL Server エラー 9002)

オプション 1: Azure Data Studio から実行可能なノートブックで直接ステップを実行する

オプション 2: 手動で手順を実行する

トランザクションログが満杯になる一般的な理由

トランザクションログが満杯になった場合の解決方法

1. ログを切り詰める

ログの切り詰めについての説明

何がログの切り詰めを妨げているか

LOG_BACKUP log_reuse_wait

データベースの復旧モデルを検討する

ログをバックアップする

トランザクションログバックアップを作成する

ACTIVE_TRANSACTION log_reuse_wait

実行時間の長いトランザクションの検出

トランザクションの強制終了

AVAILABILITY_REPLICA log_reuse_wait

CHECKPOINT log_reuse_wait

log_reuse_wait の要因に関する詳細情報

2. 満杯のディスクボリュームを解決する

ディスクの空き領域

別のディスクへのログファイルの移動

別のディスクへのログファイルの追加

推奨されるアクションのためのユーティリティスクリプト

3. ログサイズの制限を変更するか自動拡張を有効にする

ログファイルのサイズを増やすか、自動拡張を有効にする

次のステップ

フィードバック

フィードバック

その他のリソース

満杯になったトランザクション ログのトラブルシューティング (SQL Server エラー 9002)

オプション 1: Azure Data Studio から実行可能なノートブックで直接ステップを実行する

オプション 2: 手動で手順を実行する

トランザクション ログが満杯になる一般的な理由

トランザクション ログが満杯になった場合の解決方法

1. ログを切り詰める

ログの切り詰めについての説明

何がログの切り詰めを妨げているか

LOG_BACKUP log_reuse_wait

データベースの復旧モデルを検討する

ログをバックアップする

トランザクション ログ バックアップを作成する

ACTIVE_TRANSACTION log_reuse_wait

実行時間の長いトランザクションの検出

トランザクションの強制終了

AVAILABILITY_REPLICA log_reuse_wait

CHECKPOINT log_reuse_wait

log_reuse_wait の要因に関する詳細情報

2. 満杯のディスク ボリュームを解決する

ディスクの空き領域

別のディスクへのログ ファイルの移動

別のディスクへのログ ファイルの追加

推奨されるアクションのためのユーティリティ スクリプト

3. ログ サイズの制限を変更するか自動拡張を有効にする

ログ ファイルのサイズを増やすか、自動拡張を有効にする

次のステップ

フィードバック

フィードバック

その他のリソース

満杯になったトランザクションログのトラブルシューティング (SQL Server エラー 9002)

トランザクションログが満杯になる一般的な理由

トランザクションログが満杯になった場合の解決方法

トランザクションログバックアップを作成する

2. 満杯のディスクボリュームを解決する

別のディスクへのログファイルの移動

別のディスクへのログファイルの追加

推奨されるアクションのためのユーティリティスクリプト

3. ログサイズの制限を変更するか自動拡張を有効にする

ログファイルのサイズを増やすか、自動拡張を有効にする