針對 Azure SQL 資料庫的交易記錄錯誤進行疑難排解

發行項
03/18/2024

當交易記錄已滿且無法接受新的交易時，您可能會看到錯誤 9002 或 40552。當資料庫交易記錄 (由 Azure SQL 資料庫管理) 超過空間的閾值且無法繼續接受交易時，就會發生這些錯誤。這些錯誤與 SQL Server 中完整交易記錄的問題類似，但 SQL Server、Azure SQL 資料庫和 Azure SQL 受控執行個體中有不同的解決方式。

注意

本文著重於 Azure SQL 資料庫。 Azure SQL 資料庫是以最新穩定版本的 Microsoft SQL Server 資料庫引擎為基礎，因此多數內容相似，但是疑難排解選項和工具可能與 SQL Server 有所不同。

如需針對 Azure SQL 受控執行個體中的交易記錄進行疑難排解的詳細資訊，請參閱針對 Azure SQL 受控執行個體的交易記錄錯誤進行疑難排解。

如需在 SQL Server 中對交易記錄進行疑難排解的詳細資訊，請參閱完整交易記錄 (SQL Server 錯誤 9002) 的疑難排解。

自動備份與交易記錄

在 Azure SQL 資料庫中，系統會自動執行交易記錄備份。如需頻率、保留和詳細資訊，請參閱自動備份。

也會管理可用磁碟空間、資料庫檔案成長和檔案位置，因此交易記錄問題的一般原因和解決方式與 SQL Server 不同。

與 SQL Server 相似，每當成功完成記錄備份時，系統會截斷每個資料庫的交易記錄。截斷會在記錄檔中留下空白空間，這些空白空間稍後可以用於新的交易。記錄備份無法截斷記錄檔時，記錄檔會成長以容納新的交易。如果記錄檔成長到 Azure SQL 資料庫的上限，將無法進行新的寫入交易。

如需交易記錄大小的資訊，請參閱：

如需單一資料庫的虛擬核心資源限制，請參閱使用虛擬核心購買模型的單一資料庫資源限制。
如需彈性集區的虛擬核心資源限制，請參閱使用虛擬核心購買模型的彈性集區資源限制。
如需單一資料庫的 DTU 資源限制，請參閱使用 DTU 購買模型的單一資料庫資源限制。
如需彈性集區的 DTU 資源限制，請參閱使用 DTU 購買模型的彈性集區資源限制。

防止交易記錄截斷

若要找出是什麼在指定案例中防止記錄截斷，請參閱 sys.databases 中的 log_reuse_wait_desc。記錄重複使用等候會通知您，哪些狀況或原因會造成一般記錄備份無法截斷交易記錄。如需詳細資訊，請參閱 sys.databases (Transact-SQL)。

SELECT [name], log_reuse_wait_desc FROM sys.databases;

若是 Azure SQL Database，建議連線至特定的使用者資料庫以執行此查詢，而不是 master 資料庫。

sys.databases 中 log_reuse_wait_desc 的下列值可能指出防止資料庫的交易記錄截斷的原因：

log_reuse_wait_desc	診斷	所需回應
無	一般狀態。沒有項目可防止記錄遭到截斷。	不會。
CHECKPOINT	記錄截斷需要檢查點。罕見。	除非持續，否則不需要任何回應。如果持續發生，請向 Azure 支援提出支援要求。
記錄備份	需要記錄備份。	除非持續，否則不需要任何回應。如果持續發生，請向 Azure 支援提出支援要求。
使用中的備份或還原	資料庫備份正在進行中。	除非持續，否則不需要任何回應。如果持續發生，請向 Azure 支援提出支援要求。
使用中的交易	正在進行的交易使得記錄無法截斷。	因為使用中和/或未認可的交易，所以無法截斷記錄檔。請參閱下一節。
複寫	在 Azure SQL Database 中，如果啟用異動資料擷取 (CDC)，就可能發生此情況。	查詢 sys.dm_cdc_errors 並解決錯誤。如果無法解決，請向 Azure 支援提出支援要求。
AVAILABILITY_REPLICA	次要複本的同步處理正在進行中。	除非持續，否則不需要任何回應。如果持續發生，請向 Azure 支援提出支援要求。

使用中交易防止的記錄截斷

無法接受新交易的交易記錄最常見的情節是長時間執行或封鎖的交易。

執行此樣本查詢來尋找未認可或使用中的交易及其屬性。

從 sys.dm_tran_active_transactions傳回交易屬性的相關資訊。
從 sys.dm_exec_sessions 傳回工作階段連線資訊。
從 sys.dm_exec_requests 傳回 (使用中要求) 要求資訊。此查詢也可以用來識別要封鎖的工作階段，並尋找 request_blocked_by。如需詳細資訊，請參閱收集封鎖資訊。
使用 sys.dm_exec_sql_text 或 sys.dm_exec_input_buffer DMV，傳回目前要求的文字或輸入緩衝區文字。如果 sys.dm_exec_sql_text 的 text 欄位所傳回資料是 Null，則要求不在使用中，但是有未處理的交易。在此情況下，sys.dm_exec_input_buffer 的 event_info 欄位包含傳遞至資料庫引擎的陳述式。

SELECT [database_name] = db_name(s.database_id)
, tat.transaction_id, tat.transaction_begin_time, tst.session_id 
, session_open_transaction_count = tst.open_transaction_count
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, input_buffer = ib.event_info
, request_text = CASE  WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN left(est.text, 4000)
                       ELSE    SUBSTRING ( est.[text],    r.statement_start_offset/2 + 1, 
                                           CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text])) 
                                                ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
                                           END  )  END
, request_status = r.status
, request_blocked_by = r.blocking_session_id
, transaction_state = CASE tat.transaction_state    
                     WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                     WHEN 1 THEN 'The transaction has been initialized but has not started.'
                     WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                     WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                     WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
                     WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                     WHEN 6 THEN 'The transaction has been committed.'
                     WHEN 7 THEN 'The transaction is being rolled back.'
                     WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name
, azure_dtc_state    --Applies to: Azure SQL Database only
             =    CASE tat.dtc_state 
                 WHEN 1 THEN 'ACTIVE'
                 WHEN 2 THEN 'PREPARED'
                 WHEN 3 THEN 'COMMITTED'
                 WHEN 4 THEN 'ABORTED'
                 WHEN 5 THEN 'RECOVERED' END
, transaction_type = CASE tat.transaction_type    WHEN 1 THEN 'Read/write transaction'
                                             WHEN 2 THEN 'Read-only transaction'
                                             WHEN 3 THEN 'System transaction'
                                             WHEN 4 THEN 'Distributed transaction' END
, tst.is_user_transaction
, local_or_distributed = CASE tst.is_local WHEN 1 THEN 'Local transaction, not distributed' WHEN 0 THEN 'Distributed transaction or an enlisted bound session transaction.' END
, transaction_uow    --for distributed transactions. 
, s.login_time, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
, session_cpu_time = s.cpu_time, session_logical_reads = s.logical_reads, session_reads = s.reads, session_writes = s.writes
, observed = sysdatetimeoffset()
FROM sys.dm_tran_active_transactions AS tat 
INNER JOIN sys.dm_tran_session_transactions AS tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions AS s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests AS r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib 
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) AS est;

檔案管理可釋出更多空間

在 Azure SQL 資料庫彈性集區中，如果禁止截斷交易記錄，則釋放空間以用於彈性集區可以算是解決方案。不過，解決阻礙交易記錄檔截斷的根本條件是關鍵。在某些情況下，暫時建立更多磁碟空間可讓系統完成長時間執行的交易，消除阻礙交易記錄檔截斷一般交易記錄備份的條件。然而，在交易記錄再次成長之前，釋出空間可能只能暫時緩解此問題。

如需管理資料庫和彈性集區之檔案空間的詳細資訊，請參閱 Azure SQL Database 中管理資料庫的檔案空間。

錯誤 40552：因為過度使用交易記錄空間，已終止工作階段

40552: The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

若要解決此問題，請嘗試下列方法：

此問題可能會發生在任何 DML 作業中，例如插入、更新或刪除。請檢閱交易以避免不必要的寫入。嘗試透過實作批次處理或分割成多個較小的交易，來減少立即操作的資料列數目。如需詳細資訊，請參閱如何使用批次處理來改善 SQL Database 應用程式效能。
此問題可能是因為索引重建作業所導致。若要避免這個問題，請確定下列公式成立：(資料表中受影響的資料列數目) 乘以 (更新的欄位平均大小 (位元組) + 80) < 2 GB。針對大型資料表，請考慮只在資料表的某些分割區上建立分割區和執行索引維護。如需詳細資訊，請參閱建立分割區資料表及索引。
如果您使用 bcp.exe 公用程式或 System.Data.SqlClient.SqlBulkCopy 類別執行大量插入，請嘗試使用 -b batchsize 或 BatchSize 選項來限制每一筆交易中要複製到伺服器的資料列數目。如需相關資訊，請參閱 bcp Utility。
如果您要使用 ALTER INDEX 陳述式重建索引，請使用 SORT_IN_TEMPDB = ON、ONLINE = ON 和 RESUMABLE=ON 選項。使用可繼續索引可讓記錄截斷更加頻繁。如需詳細資訊，請參閱 ALTER INDEX (Transact-SQL)。

注意

如需其他資源管理錯誤的詳細資訊，請參閱資源管理錯誤。