Home Forums SQL Server 2005 Administering Transaction Log File size not reducing after transaction log backups RE: Transaction Log File size not reducing after transaction log backups

  • Note, this is a 5 year old thread.  To help troubleshoot why a transaction log file is growing, you might be able to use the default trace using a query like this:
    SELECT td.DatabaseName, td.Filename, te.name AS Event, (IntegerData*8)/1024 AS Change_MB, td.StartTime, (Duration/1000) AS Duration_sec,
      td.LoginName, td.HostName, td.ApplicationName, td.spid, td.ClientProcessID, td.IsSystem, td.SqlHandle, td.TextData
    FROM sys.traces t
      CROSS APPLY ::fn_trace_gettable(t.path, default) td
      INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id
    WHERE t.is_default = 1
      AND td.EventClass IN (92,93) --data and log autogrowths
    ORDER BY td.StartTime DESC;

    Here are a couple of articles on the subject:
    https://blogs.msdn.microsoft.com/sqlblog/2006/09/18/why-is-my-transaction-log-file-growing-rapidly-how-do-i-stop-it-from-eating-up-all-my-disk-space/
    https://sqlity.net/en/1805/eight-reasons-transaction-log-files-keep-growing/