how to identify the transactions that are causing the transaction log to grow?

Question

I have a database of 20 GB which insists in having its transaction log over 7GB.

when I used this script to find out the size of the biggest objects in that database, I see they are relatively small.

I have been using the default trace to see when this transaction log has been autogrown, but I found none.

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;

I have also tried some scripts from here, to check what transactions are filling the log, but could not find any.

I believe there must be some very long transactions going on at the same time, or at least one of more long transactions being executed.

How can I check for these (long transactions in the database)?

In LIVE this is a full recovery mode database, part of alwayson. In TEST this is a simple recovery mode database, but because all the jobs are there, the log grows to 7GB all the same.

Answer 1

Try this extended event (change the filename path accordingly). I've used it in the past to help me track down what was causing unexpected growth in my data and log files.

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

You'll need to shrink your log on the TEST server, then let the jobs run so that it grows again. This extended event will then log the SQL text of what causes the autogrowth event.