Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – Monitor SQL Transaction Log AutoGrowth for performance issues

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

 Why?

 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 ; 

 

Why are the transaction logs growing so slow?

 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

Read More

Database autogrow and slow database recovery ... - SQL Server DBA

SQL Server faster restores with instant file initialisation

Storage Checklist FAQ - SQL Server DBA

SQL LOG FILE AUTOGROW performance ... - SQL Server DBA

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer