針對 Azure SQL 受控執行個體的交易記錄錯誤進行疑難排解

適用於：Azure SQL 受控執行個體

• Azure SQL Database
• Azure SQL 受控執行個體

當交易記錄已滿且無法接受新的交易時，您可能會看到錯誤 9002 或 40552。 當資料庫交易記錄 (由 Azure SQL 資料庫或 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 受控執行個體上執行自動備份時進行追蹤，請檢閱監視備份活動。

無法管理資料庫檔案的位置和名稱，但管理員可以管理資料庫檔案和檔案自動成長設定。 交易記錄問題的一般原因和解決方式與 SQL Server 類似。

與 SQL Server 相似，每當成功完成記錄備份時，系統會截斷每個資料庫的交易記錄。 記錄截斷會從交易記錄中刪除非使用中的虛擬記錄檔 (VFS)，以釋放檔案內部的空間，但不會變更磁碟上的檔案大小。 然後記錄檔中的空白空間就能用於新的交易。 記錄備份無法截斷記錄檔時，記錄檔會成長以容納新的交易。 如果記錄檔成長到 Azure SQL 受控執行個體的上限，會無法進行新的寫入交易。

在 Azure SQL 受控執行個體 中，您可以購買附加元件儲存體，而不受計算限制。 如需詳細資訊，請參閱檔案管理以釋放更多空間。

防止交易記錄截斷

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

SELECT [name], log_reuse_wait_desc FROM sys.databases;

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

log_reuse_wait_desc	診斷	所需回應
無	一般狀態。 沒有項目可防止記錄遭到截斷。	不會。
CHECKPOINT	記錄截斷需要檢查點。 罕見。	除非持續，否則不需要任何回應。 如果持續發生，請向 Azure 支援提出支援要求。
記錄備份	需要記錄備份。	除非持續，否則不需要任何回應。 如果持續發生，請向 Azure 支援提出支援要求。
使用中的備份或還原	資料庫備份正在進行中。	除非持續，否則不需要任何回應。 如果持續發生，請向 Azure 支援提出支援要求。
使用中的交易	正在進行的交易使得記錄無法截斷。	因為使用中和/或未認可的交易，所以無法截斷記錄檔。 請參閱下一節。
複寫	在 Azure SQL 受控執行個體中，如果啟用複寫或 CDC，就可能發生此情況。	如果持續發生，請調查與 CDC 或複寫相關的代理程式。 若要針對 CDC 進行疑難排解，請在 msdb.dbo.cdc_jobs 中查詢作業。 如果不存在，請透過 sys.sp_cdc_add_job 加入。 針對複寫，請參閱針對異動複寫進行疑難排解。 如果無法解決，請向 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
, 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 受控執行個體 中，您可以購買附加元件儲存體，而不受計算限制。 例如，在 Azure 入口網站中，存取 [計算 + 儲存體] 頁面，以增加儲存體 (以 GB 為單位)。 如需交易記錄記錄大小限制的資訊，請參閱 SQL 受控執行個體的資源限制。 如需詳細資訊，請參閱管理 Azure SQL 受控執行個體中的檔案空間。

備份儲存體不會從 SQL 受控執行個體儲存體空間中扣除。 備份儲存體與執行個體的儲存體空間無關，其大小不會受限。

錯誤 9002：資料庫的交易記錄已滿

9002: The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

SQL Server 和 Azure SQL 受控執行個體發生錯誤 9002，原因相同。

寫滿交易記錄的適當回應會根據導致記錄填滿的條件而定。

若要解決錯誤 9002，請嘗試下列方法：

• 交易記錄不會遭到截斷，而且已成長為填滿所有可用空間。
  • 由於 Azure SQL 受控執行個體中的交易記錄備份是自動的，其他項目必須防止交易記錄備份活動遭到截斷。 不完整的複寫、CDC 或可用性群組同步處理可能會防止截斷，請參閱防止交易記錄截斷。
• SQL 受控執行個體保留儲存體大小已滿，而且交易記錄無法成長。
  • 將空間新增至資源限制，請參閱檔案管理以釋放更多空間。
• 交易記錄大小設定為固定最大值或自動成長已停用，因此無法成長。
  • 請參閱 ALTER DATABASE 檔案和檔案群組中的 MAXSIZE 和 FILEGROWTH 屬性。

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

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

雖然錯誤 9002 比 Azure SQL 受控執行個體的錯誤 40552 更常見，但兩者都可能發生。

若要解決錯誤 40552，請嘗試下列方法：

• 此問題可能會發生在任何 DML 作業中，例如插入、更新或刪除。 請檢閱交易以避免不必要的寫入。 嘗試透過實作批次處理或分割成多個較小的交易，來減少立即操作的資料列數目。 如需詳細資訊，請參閱如何使用批次處理來改善應用程式效能。
• 此問題可能是因為索引重建作業所導致。 若要避免這個問題，請確定下列公式成立：(資料表中受影響的資料列數目) 乘以 (更新的欄位平均大小 (位元組) + 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)。

下一步

• 了解並解析封鎖問題
• 針對 Azure SQL Database 和 Azure SQL 受控執行個體的連線問題和其他問題進行疑難排解
• 針對 SQL Database 和 Azure SQL 受控執行個體中的暫時性連線錯誤進行疑難排解