10 January,2013 by Jack Vamvas
Tracking Transaction Log autogrowth frequency and the duration can give some good hints about SQL Server performance issues.
Read more on SQL LOG FILE AUTOGROW performance troubleshooting
1) While the Transaction Log file is growing ,the transaction initiating the autogrowth must wait for the autogrowth to finish before the transaction can be written to the database. If occurring frequently and is long running an application may experience delays and even timeouts
2) Excessive auto grows can cause a high level of VLFs. This slows down processes that may need to read the transaction log files. For example , a large amount of VLFs can cause the discovery layer on a database recovery to slow down
3) The benefits of Instant File Initialization are only realized for Data File growth. Every time a log file requires growth SQL Server progresses through the Instant File Initialization. Instant file initialization uses server resources such as CPU ,memory and IO
SELECT e.name AS [EventName] , t.DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName , t.LoginName , t.SPID , t.StartTime , t.EndTime, CAST(t.Duration as DECIMAL(10,2)) / 1000000 as 'Duration_Sec' FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 f.[value] FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2 )), DEFAULT) t JOIN sys.trace_events e ON T.EventClass = e.trace_event_id WHERE (t.EventClass = 93 -- Log File Autogrowth OR t.EventClass = 95) -- Log File Shrink ORDER BY t.StartTime ;
1) The transaction log file is not assigned its own disk\LUN and disk contention is experienced.Read about Storage Checklist FAQ - SQL Server DBA
2) Attempt to size accordingly.Insufficient preallocation . Think of autogrow as a failsafe.
3) Large transaction log files are OK. SQL Server writes sequentially.
4) Autogrowth should be set with sizeable chunks. For example , if you have the log file set at 100 GB and the growth rate is 10% than the transaction log will attempt to grow 10 GB. Read more on
5) Also, analyse why the transaction log file is growing so quickly , it could be long running transactions, large batch operations not commiting effectivley, was to small in the first place