如何辨識導致事務日誌增長的事務？

我有一個 20 GB 的數據庫，它堅持讓它的事務日誌超過 7 GB。

當我使用此腳本找出該數據庫中最大對象的大小時，我發現它們相對較小。

我一直在使用預設跟踪來查看此事務日誌何時自動增長，但我沒有找到。

DECLARE @path NVARCHAR(260);

SELECT 
  @path = REVERSE(SUBSTRING(REVERSE([path]), 
  CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
  DatabaseName,
  [FileName],
  SPID,
  Duration,
  StartTime,
  EndTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 93 -- log autogrow event
ORDER BY StartTime DESC;

我還從這裡嘗試了一些腳本，以檢查哪些事務正在填充日誌，但找不到任何事務。

我相信一定有一些非常長的交易同時進行，或者至少有一個更長時間的交易正在執行。

我怎樣才能檢查這些（long transactions in the database）？

在 LIVE 中，這是一個完全恢復模式的數據庫，是 alwayson 的一部分。在 TEST 中，這是一個簡單的恢復模式數據庫，但因為所有作業都在那裡，所以日誌仍然增長到 7GB。

試試這個擴展事件（相應地更改文件名路徑）。我過去曾使用它來幫助我追踪導致數據和日誌文件意外增長的原因。

CREATE EVENT SESSION [DB Size Tracking] ON SERVER 
ADD EVENT sqlserver.database_file_size_change(
   ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'E:\ExtendedEvent\DB Size Tracking.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

您需要縮小 TEST 伺服器上的日誌，然後讓作業執行以使其再次增長。然後，此擴展事件將記錄導致自動增長事件的 SQL 文本。

引用自：https://dba.stackexchange.com/questions/189775