寫滿交易記錄疑難排解 (SQL Server 錯誤 9002)

適用于:yesSQL Server (所有支援的版本)

選項 1:透過 Azure Data Studio 直接在可執行筆記本中執行步驟

注意

嘗試開啟此筆記本之前,請先確認本機電腦上已安裝 Azure Data Studio。 若要安裝,請移至 瞭解如何安裝 Azure Data Studio

選項 2:手動遵循步驟

本主題將討論對於寫滿交易記錄的可能回應,並建議將來可採用的避免方法。

當交易記錄已滿時,SQL Server 資料庫引擎會發出 9002 錯誤。 此記錄可能會在資料庫處於線上或復原狀態時填滿。 如果記錄已滿時,資料庫正在線上,則資料庫仍會保持在線上,但只能讀取並無法更新。 如果記錄檔在復原期間填滿,Database Engine 會將資料庫標示為 RESOURCE PENDING。 不論是哪一種情況,使用者都必須採取動作,以便提供足夠的記錄空間。

注意

本文著重于 SQL Server。 如需有關 Azure SQL Database 和 Azure SQL 受控實例中此錯誤的詳細資訊,請參閱 針對 Azure SQL Database 和 Azure SQL 受控實例的交易記錄錯誤進行疑難排解。 Azure SQL Database 和 Azure SQL 受控執行個體是以最新穩定版本的 Microsoft SQL Server 資料庫引擎為基礎,因此多數內容都很相似,但是疑難排解選項和工具可能有所不同。

完整交易記錄的常見原因

完整交易記錄的適當回應取決於導致記錄填滿的狀況。 常見的原因包括:

  • 未截斷記錄檔
  • 磁片區已滿
  • 記錄大小設定為固定最大值或停用自動成長
  • 無法完成的複寫或可用性群組同步處理

如何解析完整的交易記錄

下列特定步驟可協助您找出完整交易記錄的原因,並解決問題。

1.截斷記錄檔

此問題的常見解決方案是確保對資料庫執行交易記錄備份,以確保記錄遭到截斷。 如果沒有針對具有完整交易記錄的資料庫指出最近的交易記錄記錄,問題的解決方案很簡單:繼續資料庫的一般交易記錄備份。

記錄截斷說明

截斷交易記錄和壓縮交易記錄之間有差異。 記錄截斷 通常會在交易記錄備份期間發生,而且是邏輯作業,它會移除記錄內的認可記錄,而 記錄壓縮 會藉由減少檔案大小來回收檔案系統上的實體空間。 記錄截斷發生在 虛擬記錄檔 (VLF) 界限上,而且記錄檔可能包含許多 VLF。 只有在要回收的記錄檔內有空白空間時,才能壓縮記錄檔。 單獨壓縮記錄檔無法解決完整記錄檔的問題,相反地,您必須探索記錄檔已滿的原因,而且無法截斷。

警告

為壓縮檔案所移動的資料可散佈至檔案中的任何可用位置。 這會導致索引片段,而且可能會降低搜尋索引範圍之查詢的效能。 若要消除資料片段,可考慮在壓縮之後重建該檔案的索引。 如需詳細資訊,請參閱 壓縮資料庫

什麼是防止記錄截斷?

若要探索在指定案例中防止記錄截斷的情況,請使用 log_reuse_wait 目錄檢視的 sys.databaseslog_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 db. Use a DMV which supports all supported versions

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://docs.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)), synhcronization_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://docs.microsoft.com/en-us/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' 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復原模式,但您並未備份交易記錄。 您應該仔細考慮每個資料庫復原模式:在 FULL 或 BULK LOGGED 復原模式下的所有資料庫上執行交易記錄備份,以將發生錯誤 9002 的次數降到最低。 如需詳細資訊,請參閱復原模式

備份記錄

在 FULL 或 BULK_LOGGED 復原模式下,如果最近尚未備份交易記錄,備份可能會造成無法截斷記錄。 您必須備份交易記錄,以允許釋放記錄檔記錄並截斷記錄。 如果記錄從未備份過, 您必須建立兩個記錄備份 ,讓 Database Engine 將記錄截斷到最後一個備份的點。 截斷記錄會釋放新記錄檔記錄的邏輯空間。 若要讓記錄檔重新填滿,請定期且更頻繁地進行記錄備份。 如需詳細資訊,請參閱復原模式

伺服器實例上所有 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 bs	
LEFT OUTER JOIN msdb.dbo.backupmediafamily 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 命令) 加以解析 (。

探索長時間執行的交易

非常長時間執行的交易可能會造成交易記錄被填滿。 若要尋找長時間執行的交易,請使用下列其中一種方式:

這個動態管理檢視傳回有關資料庫層級之交易的資訊。 對於長時間執行的交易,較重要的資料行包括第一筆記錄檔記錄的時間 (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://docs.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。

如果停用自動成長,資料庫會上線,且磁片上有足夠的空間可用,請執行下列其中一項:

注意

無論是哪一種情況,如果已達到目前的大小限制,都需增加 MAXSIZE 值。

另請參閱

ALTER DATABASE (Transact-SQL)
管理交易記錄檔的大小
交易記錄備份 (SQL Server)
sp_add_log_file_recover_suspect_db (Transact-SQL)
MSSQLSERVER_9002
記錄檔結構如何影響資料庫復原時間 - Microsoft 技術社群