寫滿交易記錄疑難排解 (SQL Server 錯誤 9002)
適用於：
SQL Server
本文適用於 SQL Server 執行個體。
注意
本文著重於 SQL Server。 如需 Azure SQL 平台中此錯誤的更具體資訊，請參閱針對 Azure SQL 資料庫的交易記錄錯誤進行疑難排解以及針對 Azure SQL 受控執行個體的交易記錄錯誤進行疑難排解。 Azure SQL Database 和 Azure SQL 受控執行個體是以最新穩定版本的 Microsoft SQL Server 資料庫引擎為基礎，因此多數內容都很相似，但是疑難排解選項和工具可能有所不同。
選項 1：透過 Azure Data Studio 直接在可執行筆記本中執行步驟
注意
嘗試開啟此筆記本之前，請先檢查本機電腦上是否已安裝 Azure Data Studio。 若要安裝，請前往了解如何安裝 Azure Data Studio。
選項 2：手動遵循步驟
本文將討論對於寫滿交易記錄的可能回應，並建議將來可採用的避免方法。
當交易記錄已滿時，SQL Server 資料庫引擎就會發出 9002 錯誤。 此記錄可能會在資料庫處於線上或復原狀態時填滿。 如果記錄已滿時，資料庫正在線上，則資料庫仍會保持在線上，但只能讀取並無法更新。 如果此記錄是在復原期間填滿，則資料庫引擎會將資料庫標示為 RESOURCE PENDING。 不論是哪一種情況，使用者都必須採取動作，以便提供足夠的記錄空間。
寫滿交易記錄的常見原因
寫滿交易記錄的適當回應會根據導致記錄填滿的條件而定。 常見的原因包括：
- 記錄未遭到截斷
- 磁碟區已滿
- 記錄大小設定為固定最大值或停用自動成長
- 無法完成的複寫或可用性群組同步處理
如何解決交易記錄已滿的情況
下列特定步驟可協助您找出寫滿交易記錄的原因，並解決問題。
1. 截斷記錄
此問題的常見解決方案是確保對資料庫執行交易記錄備份，以確保記錄遭到截斷。 如果沒有針對具有寫滿交易記錄的資料庫指出最近的交易記錄歷程記錄，則問題的解決方法很簡單：繼續進行資料庫的定期交易記錄備份。
記錄截斷說明
截斷交易記錄和壓縮交易記錄之間有差異。 記錄截斷通常會在交易記錄備份期間發生，而且是一項邏輯作業，它會移除記錄內的已認可記錄，而記錄壓縮會透過減少檔案大小來回收檔案系統上的實體空間。 記錄截斷發生在虛擬記錄檔 (VLF) 界限上，而記錄檔可能包含許多 VFS。 只有在記錄檔內有空的空間可回收時，才能壓縮記錄檔。 僅壓縮記錄檔無法解決完整記錄檔的問題。 相反地，您必須探索記錄檔已滿且無法截斷的原因。
警告
為壓縮檔案所移動的資料可散佈至檔案中的任何可用位置。 如此會造成索引片段，並可能導致大範圍之索引搜尋的查詢效能變慢。 若要消除資料片段，可考慮在壓縮之後重建該檔案的索引。 如需詳細資訊，請參閱壓縮資料庫。
什麼會防止記錄截斷？
若要探索指定情況下無法進行記錄截斷的原因，請使用 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 deferred transaction is keeping log from being truncated. You can attempt a log backup to free space or complete/rollback long transaction'
WHEN log_reuse_wait = 5 THEN 'Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only)'
WHEN log_reuse_wait = 6 THEN 'During transactional replication, transactions relevant to the publications are still undelivered to the distribution database. Investigate the status of agents involved in replication or Changed Data Capture (CDC). (Full recovery model only.)'
WHEN log_reuse_wait = 7 THEN 'A database snapshot is being created. This is a routine, and typically brief, cause of delayed log truncation.'
WHEN log_reuse_wait = 8 THEN 'A transaction log scan is occurring. This is a routine, and typically a brief cause of delayed log truncation.'
WHEN log_reuse_wait = 9 THEN 'A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model only.)'
WHEN log_reuse_wait = 13 THEN 'If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN).'
WHEN log_reuse_wait = 16 THEN 'An In-Memory OLTP checkpoint has not occurred since the last log truncation, or the head of the log has not yet moved beyond a VLF.'
ELSE 'None' END,
sdb.database_id,
sdb.recovery_model_desc,
lsu.used_log_space_in_bytes / 1024 as Used_log_size_MB,
lsu.total_log_size_in_bytes / 1024 as Total_log_size_MB,
100 - lsu.used_log_space_in_percent as Percent_Free_Space
INTO #CannotTruncateLog_Db
FROM sys.databases AS sdb INNER JOIN #dm_db_log_space_usage lsu ON sdb.database_id = lsu.database_id
WHERE log_reuse_wait > 0
SELECT * FROM #CannotTruncateLog_Db
DECLARE no_truncate_db CURSOR FOR
SELECT log_reuse_wait, log_reuse_wait_desc, DbName, database_id, recovery_model_desc FROM #CannotTruncateLog_Db;
OPEN no_truncate_db
FETCH NEXT FROM no_truncate_db into @log_reuse_wait, @log_reuse_wait_desc, @dbname, @database_id, @recovery_model_desc
WHILE @@FETCH_STATUS = 0
BEGIN
if (@log_reuse_wait > 0)
select '-- ''' + @dbname + ''' database has log_reuse_wait = ' + @log_reuse_wait_desc + ' --' as 'Individual Database Report'
if (@log_reuse_wait = 1)
BEGIN
select 'Consider running the checkpoint command to attempt resolving this issue or further t-shooting may be required on the checkpoint process. Also, examine the log for active VLFs at the end of file' as Recommendation
select 'USE ''' + @dbname+ '''; CHECKPOINT' as CheckpointCommand
select 'select * from sys.dm_db_log_info(' + CONVERT(varchar,@database_id)+ ')' as VLF_LogInfo
END
else if (@log_reuse_wait = 2)
BEGIN
select 'Is '+ @recovery_model_desc +' recovery model the intended choice for ''' + @dbname+ ''' database? Review recovery models and determine if you need to change it. https://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 encountered any errors' as Recommendation
select 'DBCC OPENTRAN (''' + @dbname + ''')' as CheckOldestNonDistributedTran
select 'select top 5 * from distribution..MSlogreader_history where runstatus in (6, 5) or error_id <> 0 and agent_id = find_in_mslogreader_agents_table order by time desc ' as LogReaderAgentState
END
else if (@log_reuse_wait = 9)
BEGIN
select 'Always On transactions still undelivered from primary database ''' +@dbname+ ''' to Secondary replicas. Check the Health of AG nodes and if there is latency is Log block movement to Secondaries' as Recommendation
select 'select availability_group=cast(ag.name as varchar(30)), primary_replica=cast(ags.primary_replica as varchar(30)),primary_recovery_health_desc=cast(ags.primary_recovery_health_desc as varchar(30)), synchronization_health_desc=cast(ags.synchronization_health_desc as varchar(30)),ag.failure_condition_level, ag.health_check_timeout, automated_backup_preference_desc=cast(ag.automated_backup_preference_desc as varchar(10)) from sys.availability_groups ag join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id' as CheckAGHealth
select 'SELECT group_name=cast(arc.group_name as varchar(30)), replica_server_name=cast(arc.replica_server_name as varchar(30)), node_name=cast(arc.node_name as varchar(30)),role_desc=cast(ars.role_desc as varchar(30)), ar.availability_mode_Desc, operational_state_desc=cast(ars.operational_state_desc as varchar(30)), connected_state_desc=cast(ars.connected_state_desc as varchar(30)), recovery_health_desc=cast(ars.recovery_health_desc as varchar(30)), synchronization_health_desc=cast(ars.synchronization_health_desc as varchar(30)), ars.last_connect_error_number, last_connect_error_description=cast(ars.last_connect_error_description as varchar(30)), ars.last_connect_error_timestamp, primary_role_allow_connections_desc=cast(ar.primary_role_allow_connections_desc as varchar(30)) from sys.dm_hadr_availability_replica_cluster_nodes arc join sys.dm_hadr_availability_replica_cluster_states arcs on arc.replica_server_name=arcs.replica_server_name join sys.dm_hadr_availability_replica_states ars on arcs.replica_id=ars.replica_id join sys.availability_replicas ar on ars.replica_id=ar.replica_id join sys.availability_groups ag on ag.group_id = arcs.group_id and ag.name = arc.group_name ORDER BY cast(arc.group_name as varchar(30)), cast(ars.role_desc as varchar(30))' as CheckReplicaHealth
select 'select database_name=cast(drcs.database_name as varchar(30)), drs.database_id, drs.group_id, drs.replica_id, drs.is_local,drcs.is_failover_ready,drcs.is_pending_secondary_suspend, drcs.is_database_joined, drs.is_suspended, drs.is_commit_participant, suspend_reason_desc=cast(drs.suspend_reason_desc as varchar(30)), synchronization_state_desc=cast(drs.synchronization_state_desc as varchar(30)), synchronization_health_desc=cast(drs.synchronization_health_desc as varchar(30)), database_state_desc=cast(drs.database_state_desc as varchar(30)), drs.last_sent_lsn, drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, drs.last_hardened_time,drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.low_water_mark_for_ghosts, drs.recovery_lsn, drs.truncation_lsn, pr.file_id, pr.error_type, pr.page_id, pr.page_status, pr.modification_time from sys.dm_hadr_database_replica_cluster_states drcs join sys.dm_hadr_database_replica_states drs on drcs.replica_id=drs.replica_id and drcs.group_database_id=drs.group_database_id left outer join sys.dm_hadr_auto_page_repair pr on drs.database_id=pr.database_id order by drs.database_id' as LogMovementHealth
select 'For more information see https://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，要考慮的最常見動作是檢閱資料庫復原模型，以及備份資料庫的交易記錄。
請考慮資料庫的復原模式
交易記錄可能無法以 LOG_BACKUP log_reuse_wait 類別截斷，因為您從未備份過。 在許多情況下，您的資料庫使用 FULL 或 BULK_LOGGED 復原模式，但您並未備份交易記錄。 您應該仔細考慮每個資料庫復原模式：在 FULL 或 BULK LOGGED 復原模式的所有資料庫上執行定期交易記錄備份，以將發生錯誤 9002 的次數降到最低。 如需詳細資訊，請參閱復原模式。
備份記錄
在 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'
SqlBackup (SMO)
重要
如果資料庫已損毀，請參閱結尾記錄備份 (SQL Server)。
ACTIVE_TRANSACTION log_reuse_wait
針對 ACTIVE_TRANSACTION 原因進行疑難排解的步驟包括探索長時間執行的交易，並加以解析 (在某些情況下使用 KILL 命令進行)。
探索長時間執行的交易
非常長時間執行的交易可能會造成交易記錄被填滿。 若要尋找長時間執行的交易，請使用下列其中一種方式：
這個動態管理檢視傳回有關資料庫層級之交易的資訊。 對於長時間執行的交易，較重要的資料行包括第一筆記錄檔記錄的時間 (database_transaction_begin_time)、交易的目前狀態 (database_transaction_state)和交易記錄之 BEGIN 記錄的 記錄序號 (LSN)(database_transaction_begin_lsn)。
- DBCC OPENTRAN。 此陳述式可讓您識別交易擁有者的使用者識別碼，如此就可以追蹤交易來源，以便更有條理地終止交易 (進行認可而非回復)。
刪除交易
有時您必須結束交易；您可能必須使用 KILL 陳述式。 使用 KILL 陳述式時，要非常小心，尤其是執行您不想結束的重要處理序時。
CHECKPOINT log_reuse_wait
自從上次記錄截斷後尚未出現任何檢查點，或是記錄標頭尚未移到虛擬記錄檔 (VLF) 的範圍之外。 (所有復原模式)
這是延遲記錄截斷的一般原因。 如果延遲，請考慮在資料庫上執行 CHECKPOINT 命令，或檢查記錄 VFS。
USE dbname; CHECKPOINT
select * from sys.dm_db_log_info(db_id('dbname'))
AVAILABILITY_REPLICA log_reuse_wait
當次要複本上尚未強化主要 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 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 選項的非零成長遞增，藉以開啟自動成長。 請參閱 SQL Server 中自動成長和自動壓縮設定的考量。
注意
無論是哪一種情況，如果已達到目前的大小限制，都需增加 MAXSIZE 值。