適用於：
SQL Server
本文適用於 SQL Server 執行個體。
注意
本文著重於 SQL Server。 如需 Azure SQL 平臺中此錯誤的詳細資訊，請參閱針對 Azure SQL Database 的事務歷史記錄錯誤進行疑難解答 ，以及 使用 Azure SQL 受控實例針對事務歷史記錄錯誤進行疑難解答。 Azure SQL Database 和 Azure SQL 受控執行個體是以最新穩定版本的 Microsoft SQL Server 資料庫引擎為基礎，因此多數內容都很相似，但是疑難排解選項和工具可能有所不同。
選項 1：透過 Azure Data Studio 直接在可執行的筆記本中執行步驟
嘗試開啟此筆記本之前，請先檢查本機電腦上是否已安裝 Azure Data Studio。 若要安裝，請移至 下載並安裝 Azure Data Studio。
選項 2：手動遵循步驟
本文將討論對於寫滿交易記錄的可能回應，並建議將來可採用的避免方法。
當事務歷史記錄已滿時，SQL Server 資料庫引擎會發出 9002 錯誤。 此記錄可能會在資料庫處於線上或復原狀態時填滿。 如果記錄已滿時，資料庫正在線上，則資料庫仍會保持在線上，但只能讀取並無法更新。 如果記錄在復原期間填滿，Database Engine 會將資料庫標示為 RESOURCE PENDING。 不論是哪一種情況，使用者都必須採取動作，以便提供足夠的記錄空間。
寫滿交易記錄的常見原因
寫滿交易記錄的適當回應會根據導致記錄填滿的條件而定。 常見的原因包括：
- 記錄未遭到截斷
- 磁碟區已滿
- 記錄大小設定為固定最大值或停用自動成長
- 無法完成的複寫或可用性群組同步處理
請遵循這些特定步驟，協助您找出完整事務歷史記錄的原因並解決問題。
1.截斷記錄檔
此問題的常見解決方案是確保針對資料庫執行交易日志備份，這樣可以確保日志被截斷。 如果沒有針對具有寫滿交易記錄的資料庫指出最近的交易記錄歷程記錄，則問題的解決方法很簡單：繼續進行資料庫的定期交易記錄備份。
如需詳細資訊，請參閱 管理事務歷史記錄檔的大小 和 壓縮檔案。
記錄截斷說明
截斷交易記錄和壓縮交易記錄之間有差異。 記錄截斷 通常在交易記錄備份期間發生，這是一種邏輯操作，用來移除記錄中的已提交記錄，而 記錄縮減 則通過減少檔案大小來回收檔案系統上的物理空間。 記錄截斷發生在 虛擬記錄檔 （VLF） 界限上，而記錄檔可能包含許多 VDF。 只有在記錄檔內有空的空間可回收時，才能壓縮記錄檔。 僅壓縮記錄檔無法解決完整記錄檔的問題。 相反地，您必須探索記錄檔已滿且無法截斷的原因。
警告
為壓縮檔案所移動的資料可散佈至檔案中的任何可用位置。 如此會造成索引片段，並可能導致大範圍之索引搜尋的查詢效能變慢。 若要消除資料片段，可考慮在壓縮之後重建該檔案的索引。 如需詳細資訊，請參閱 壓縮資料庫。
什麼會防止記錄截斷？
若要探索指定情況下無法進行記錄截斷的原因，請使用 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. / 1024. AS Used_log_size_MB,
lsu.total_log_size_in_bytes / 1024. / 1024. AS Total_log_size_MB,
100 - lsu.used_log_space_in_percent AS Percent_Free_Space
INTO #CannotTruncateLog_Db
FROM sys.databases AS sdb
INNER JOIN #dm_db_log_space_usage AS lsu
ON sdb.database_id = lsu.database_id
WHERE log_reuse_wait > 0;
SELECT * FROM #CannotTruncateLog_Db;
DECLARE no_truncate_db CURSOR FOR
SELECT
log_reuse_wait,
log_reuse_wait_desc,
DbName,
database_id,
recovery_model_desc
FROM #CannotTruncateLog_Db;
OPEN no_truncate_db;
FETCH NEXT FROM no_truncate_db
INTO
@log_reuse_wait,
@log_reuse_wait_desc,
@dbname,
@database_id,
@recovery_model_desc;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@log_reuse_wait > 0)
BEGIN
SELECT '-- ' + QUOTENAME(@dbname) + ' database has log_reuse_wait = ' + @log_reuse_wait_desc + ' --' AS 'Individual Database Report';
END
IF (@log_reuse_wait = 1)
BEGIN
SELECT 'Consider running the checkpoint command to attempt resolving this issue or further t-shooting may be required on the checkpoint process. Also, examine the log for active VLFs at the end of file' AS Recommendation;
SELECT 'USE ' + QUOTENAME(@dbname) + '; CHECKPOINT' AS CheckpointCommand;
SELECT 'SELECT * FROM sys.dm_db_log_info(' + CONVERT (VARCHAR, @database_id) + ')' AS VLF_LogInfo;
END
ELSE IF (@log_reuse_wait = 2)
BEGIN
SELECT 'Is ' + @recovery_model_desc + ' recovery model the intended choice for ' + QUOTENAME(@dbname) + ' database? Review recovery models and determine if you need to change it. https://learn.microsoft.com/sql/relational-databases/backup-restore/recovery-models-sql-server' AS RecoveryModelChoice;
SELECT 'To truncate the log consider performing a transaction log backup on database ' + QUOTENAME(@dbname) + ' which is in ' + @recovery_model_desc + ' recovery model. Be mindful of any existing log backup chains that could be broken' AS Recommendation;
SELECT 'BACKUP LOG ' + QUOTENAME(@dbname) + ' TO DISK = ''some_volume:\some_folder\' + QUOTENAME(@dbname) + '_LOG.trn '';' AS BackupLogCommand;
END
ELSE IF (@log_reuse_wait = 3)
BEGIN
SELECT 'Either wait for or cancel any active backups currently running for database ' + QUOTENAME(@dbname) + '. To check for backups, run this command:' AS Recommendation;
SELECT 'SELECT * FROM sys.dm_exec_requests WHERE command LIKE ''backup%'' OR command LIKE ''restore%''' AS FindBackupOrRestore;
END
ELSE IF (@log_reuse_wait = 4)
BEGIN
SELECT 'Active transactions currently running for database ' + QUOTENAME(@dbname) + '. To check for active transactions, run these commands:' AS Recommendation;
SELECT 'DBCC OPENTRAN (' + QUOTENAME(@dbname) + ')' AS FindOpenTran;
SELECT 'SELECT database_id, db_name(database_id) AS dbname, database_transaction_begin_time, database_transaction_state, database_transaction_log_record_count, database_transaction_log_bytes_used, database_transaction_begin_lsn, stran.session_id FROM sys.dm_tran_database_transactions dbtran LEFT OUTER JOIN sys.dm_tran_session_transactions stran ON dbtran.transaction_id = stran.transaction_id WHERE database_id = ' + CONVERT (VARCHAR, @database_id) AS FindOpenTransAndSession;
END
ELSE IF (@log_reuse_wait = 5)
BEGIN
SELECT 'Database Mirroring for database ' + QUOTENAME(@dbname) + ' is behind on synchronization. To check the state of DBM, run the commands below:' AS Recommendation;
SELECT 'SELECT db_name(database_id), mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL and mirroring_state <> 4 AND database_id = ' + CONVERT (sysname, @database_id) AS CheckMirroringStatus;
SELECT 'Database Mirroring for database ' + QUOTENAME(@dbname) + ' may be behind: check unsent_log, send_rate, unrestored_log, recovery_rate, average_delay in this output' AS Recommendation;
SELECT 'EXECUTE msdb.sys.sp_dbmmonitoraddmonitoring 1; EXECUTE msdb.sys.sp_dbmmonitorresults ' + QUOTENAME(@dbname) + ', 5, 0; WAITFOR DELAY ''00:01:01''; EXECUTE msdb.sys.sp_dbmmonitorresults ' + QUOTENAME(@dbname) + '; EXECUTE msdb.sys.sp_dbmmonitordropmonitoring' AS CheckMirroringStatusAnd;
END
ELSE IF (@log_reuse_wait = 6)
BEGIN
SELECT 'Replication transactions still undelivered FROM publisher database ' + QUOTENAME(@dbname) + ' to Distribution database. Check the oldest non-distributed replication transaction. Also check if the Log Reader Agent is running and if it has encountered any errors' AS Recommendation;
SELECT 'DBCC OPENTRAN (' + QUOTENAME(@dbname) + ')' AS CheckOldestNonDistributedTran;
SELECT 'SELECT top 5 * FROM distribution..MSlogreader_history WHERE runstatus in (6, 5) OR error_id <> 0 AND agent_id = find_in_mslogreader_agents_table ORDER BY time desc ' AS LogReaderAgentState;
END
ELSE IF (@log_reuse_wait = 9)
BEGIN
SELECT 'Always On transactions still undelivered FROM primary database ' + QUOTENAME(@dbname) + ' to Secondary replicas. Check the Health of AG nodes and if there is latency is Log block movement to Secondaries' AS Recommendation;
SELECT 'SELECT availability_group = CAST(ag.name AS VARCHAR(30)), primary_replica = CAST(ags.primary_replica AS VARCHAR(30)), primary_recovery_health_desc = CAST(ags.primary_recovery_health_desc AS VARCHAR(30)), synchronization_health_desc = CAST(ags.synchronization_health_desc AS VARCHAR(30)), ag.failure_condition_level, ag.health_check_timeout, automated_backup_preference_desc = CAST(ag.automated_backup_preference_desc AS VARCHAR(10)) FROM sys.availability_groups ag join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id' AS CheckAGHealth;
SELECT 'SELECT group_name = CAST(arc.group_name AS VARCHAR(30)), replica_server_name = CAST(arc.replica_server_name AS VARCHAR(30)), node_name = CAST(arc.node_name AS VARCHAR(30)), role_desc = CAST(ars.role_desc AS VARCHAR(30)), ar.availability_mode_Desc, operational_state_desc = CAST(ars.operational_state_desc AS VARCHAR(30)), connected_state_desc = CAST(ars.connected_state_desc AS VARCHAR(30)), recovery_health_desc = CAST(ars.recovery_health_desc AS VARCHAR(30)), synchronization_health_desc = CAST(ars.synchronization_health_desc AS VARCHAR(30)), ars.last_connect_error_number, last_connect_error_description = CAST(ars.last_connect_error_description AS VARCHAR(30)), ars.last_connect_error_timestamp, primary_role_allow_connections_desc = CAST(ar.primary_role_allow_connections_desc AS VARCHAR(30)) FROM sys.dm_hadr_availability_replica_cluster_nodes arc join sys.dm_hadr_availability_replica_cluster_states arcs on arc.replica_server_name=arcs.replica_server_name join sys.dm_hadr_availability_replica_states ars on arcs.replica_id=ars.replica_id join sys.availability_replicas ar on ars.replica_id=ar.replica_id join sys.availability_groups ag on ag.group_id = arcs.group_id and ag.name = arc.group_name ORDER BY CAST(arc.group_name AS VARCHAR(30)), CAST(ars.role_desc AS VARCHAR(30))' AS CheckReplicaHealth;
SELECT 'SELECT database_name = CAST(drcs.database_name AS VARCHAR(30)), drs.database_id, drs.group_id, drs.replica_id, drs.is_local, drcs.is_failover_ready, drcs.is_pending_secondary_suspend, drcs.is_database_joined, drs.is_suspended, drs.is_commit_participant, suspend_reason_desc = CAST(drs.suspend_reason_desc AS VARCHAR(30)), synchronization_state_desc = CAST(drs.synchronization_state_desc AS VARCHAR(30)), synchronization_health_desc = CAST(drs.synchronization_health_desc AS VARCHAR(30)), database_state_desc = CAST(drs.database_state_desc AS VARCHAR(30)), drs.last_sent_lsn, drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.low_water_mark_for_ghosts, drs.recovery_lsn, drs.truncation_lsn, pr.file_id, pr.error_type, pr.page_id, pr.page_status, pr.modification_time FROM sys.dm_hadr_database_replica_cluster_states drcs join sys.dm_hadr_database_replica_states drs on drcs.replica_id=drs.replica_id and drcs.group_database_id=drs.group_database_id left outer join sys.dm_hadr_auto_page_repair pr on drs.database_id=pr.database_id order by drs.database_id' AS LogMovementHealth;
SELECT 'For more information see https://learn.microsoft.com/troubleshoot/sql/availability-groups/error-9002-transaction-log-large' AS OnlineDOCResource;
END
ELSE IF (@log_reuse_wait IN (10, 11, 12, 14))
BEGIN
SELECT 'This state is not documented and is expected to be rare and short-lived' AS Recommendation;
END
ELSE IF (@log_reuse_wait = 13)
BEGIN
SELECT 'The oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation.' AS Finding;
SELECT 'This state should be short-lived, but if you find it is taking a long time, you can consider disabling Indirect Checkpoint temporarily' AS Recommendation;
SELECT 'ALTER DATABASE ' + QUOTENAME(@dbname) + ' SET TARGET_RECOVERY_TIME = 0 SECONDS;' AS DisableIndirectCheckpointTemporarily;
END
ELSE IF (@log_reuse_wait = 16)
BEGIN
SELECT 'For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint (includes both disk-based and memory-optimized tables)' AS Finding;
SELECT 'Review https://learn.microsoft.com/archive/blogs/sqlcat/logging-and-checkpoint-process-for-memory-optimized-tables-2' AS ReviewBlog;
SELECT 'USE ' + QUOTENAME(@dbname) + '; CHECKPOINT;' AS RunCheckpoint;
END
FETCH NEXT FROM no_truncate_db INTO
@log_reuse_wait,
@log_reuse_wait_desc,
@dbname,
@database_id,
@recovery_model_desc;
END
CLOSE no_truncate_db;
DEALLOCATE no_truncate_db;
重要
如果資料庫在發生 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 恢復模式，但您並未備份事務歷史記錄。 您應該仔細考慮每個資料庫的恢復模式：在FULL或BULK_LOGGED恢復模式中，對所有資料庫執行定期交易記錄備份，以將錯誤 9002 的發生次數降到最低。 如需詳細資訊，請參閱
備份記錄
在 FULL 或 BULK_LOGGED 恢復模式下，如果最近尚未備份事務歷史記錄，備份可能是阻礙記錄截斷的功能。 您必須備份交易記錄，以允許釋放記錄檔記錄和截斷記錄。 如果從未備份記錄檔， 您必須建立兩個記錄備份 ，以允許 Database Engine 將記錄截斷到上次備份的點。 截斷記錄可針對新記錄檔的記錄釋出邏輯空間。 若要防止記錄檔再度被填滿，請定期並更頻繁地進行記錄備份。 如需詳細資訊，請參閱
在伺服器執行個體上的所有 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） 。
ACTIVATE_TRANSACTION 活動交易 log_reuse_wait 日誌重用等待
疑難解答 ACTIVE_TRANSACTION 原因的步驟包括探索長時間執行的交易並加以解決（在某些情況下，請使用 KILL 命令來執行此動作）。
探索長時間執行的交易
長時間執行的交易可能會導致交易日誌將填滿。 若要尋找長時間執行的交易，請使用下列其中一個選項：
sys.dm_tran_database_transactions：
這個動態管理檢視傳回有關資料庫層級之交易的資訊。 對於長時間執行的交易，重要的欄位包括第一筆日誌記錄的時間（
database_transaction_begin_time）、交易的當前狀態（database_transaction_state）以及交易日誌中記錄的日誌序列號（LSN）（BEGINdatabase_transaction_begin_lsn）。-
此陳述式可讓您識別交易擁有者的使用者識別碼，如此就可以追蹤交易來源，以便更有條理地終止交易 (進行認可而非回復)。
刪除交易
有時候，您只需要結束交易;您可能必須使用 KILL 語句。 請特別小心使用KILL語句，尤其是在您不希望終止的關鍵過程正在運行時。
檢查點 log_reuse_wait
自上次記錄截斷以來，在所有的還原模式下，都沒有發生任何檢查點，或者記錄檔的開頭尚未移動超過虛擬記錄檔（VLF）。
這是延遲記錄截斷的一般原因。 如果延遲，請考慮在資料庫上執行 CHECKPOINT 命令，或檢查記錄 VDF。
USE dbname;
CHECKPOINT;
SELECT * FROM sys.dm_db_log_info(db_id('dbname'));
可用性副本 日誌重用等待
當主要 Always On 可用性群組 複本上的交易變更尚未在次要複本上強化時，主要複本的交易記錄檔無法截斷。 這可能會導致記錄成長，而且可能會發生次要複本設定為同步或非同步認可模式。 如需如何針對這類問題進行疑難解答的資訊，請參閱 錯誤 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 TB。
如果停用自動成長、資料庫在線上，而且磁碟上有足夠的可用空間，請考慮執行下列步驟：
手動增加檔案大小以產生單一成長遞增。 這些是關於日誌大小及成長的一般建議。
使用
ALTER DATABASE語句設定FILEGROWTH選項的非零成長增量，以開啟自動成長。 請參閱 SQL Server 中自動成長和自動縮減設定的考慮事項。
注意
不論是哪一種情況，如果達到目前的大小限制，請增加 MAXSIZE 值。