SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size

14 January,2013 by Jack Vamvas

Long running SQL transactions can cause the SQL Transaction Log file to autogrow. When the transaction log file is autogrowing , the transaction initiating the autogrow must wait for the autogrow to finish before the transaction is written to the database. If occurring frequently and is long running the application may experience delays. Read more details on : SQL Server – Monitor SQL Transaction Log AutoGrowth for performance issues

The problem is further compounded when the chunks of growth are large. 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.

SQL Server writes in sequential order to the transaction log file. This means that maintaining large Transaction Log files is not counter productive, assuming other transaction log files are sized accordingly and there is sufficient disk space.

An example scenario where preallocating large transaction log files could have benefits :

Type of activity – very long running transaction with sparse COMMIT levels. As it’s an active transaction the log remains active. This is not a case of “avoid long running transactions”. It is the nature of the application.

Frequency – The long running transactions  are frequent with multiple jobs running

Recovery Model – The recovery model of the database is SIMPLE. The assumptions are : a)some data loss is acceptable b) the last good backup with DIFFERENTIAL or FULL is acceptable

Frequency of transaction log backup – There are no transaction log backups

First step is some Capacity Planning.  Any transaction log decision depends on characteristics of the environment , therefore  monitoring the nature of transactions and growth rates is essential

Second step – how do you choose a default size?

SQL Server by default is : Log: initial size 1MB; Autogrowth: by 10 percent, unrestricted growth. It’s not ideal , but what is ideal? If you know one transaction will generate 30 GB of transaction logs , why would you leave it at the default size?

On the basis of information gathered

1)       Preallocate SQL transaction log to a good initial size therefore minimizing autogrows

2)       Transaction logs always do zero initialization – therefore if a large growth rate after the initial size  then it may take time

Read More on Transaction Logs

How to find sql transactions with large logs 

SQL Server – Transaction Log files sequential - SQL Server DBA

Read sql transaction Logs with ::fn_dblog - SQL Server DBA

SQL Server faster restores with instant file initialisation


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


Verify your Comment

Previewing your Comment

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

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.


Post a comment on SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size

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