Summary

Just about every search result you get for “Transaction Log Growth” on the internet will inevitably have a result with someone asking how to shrink or truncate their transaction log because it has grown too large. I've even written an article that skims over how to do this for SQLServerCentral.comcalled "Introduction to the Transaction Log". If you're unfamiliar with the Transaction Log you may want to review this article and my April posting called "A Brief Introduction to Transaction Logs". How about the times you need to grow your log? I did a recent search and had trouble finding a clear cut discussion or article about this.

In my articles introducing the transaction log I've recommended that you should allow the log to grow to the appropriate size, and even allow extra space for the occasional and abnormal transaction spikes you will see over time. The primary purpose behind this recommendation is to minimize the data loss risks and the amount of resources thrust upon your server due to SQL Server automatically increasing the transaction log size.

This article will discuss why you should grow your transaction log, what options are available to you, and how to grow your transaction log.

Why should I grow my transaction log?

The first, and foremost, reason is simply because your database can no longer be updated (no new data, no changing of data) once your transaction log file is filled to capacity. The database becomes a “READ-ONLY” database. You will receive an error code 9002 from SQL Server once the log can no longer append transactions because it is full.

Another reason is ability to recover your database; if the log is full you can't recover from a database failure. The Database Engine will mark the Database as RESOURCE PENDING.

The final reason is performance. If you leave only just enough room for your regular interval of transactions between log backups then you run the risk of the error 9002, as stated above. If the log settings are at default then SQL Server will auto-grow the log when it needs additional log space to record the pending transaction. Obviously growing the log takes server resources for this operation alone; but, the less known factor is that the pending transaction MUST WAIT for the log to increase in size before it can be recorded. This equates to a ‘long running transaction’, which then potentially starts a chain of pending transactions; which means the server needs to work harder for longer to catch up if there are many pending transactions. All of this adds up and eventually will impact the performance of the server.

What are my options?

Essentially there are two options. First option is to expand your log file. This, if proactively performed, could be a scheduled event that may be performed at the off-peak time of the server. Generally this is the best option from an administration stand point because it leaves a single file to administer. This method can be either manually initiated using the ALTER DATABASE command, or it can be automatically performed by SQL Server if the AUTO-GROWTH option is selected (default setting).

The secondary option is to add a log file, or multiple log files. Typically this would be done when one needs the additional log file space placed onto another drive. A misconception is that using multiple log files will improve your transaction log performance. This is NOT true! SQL Server does not have striping abilities when it comes to writing log files. You may recall that SQL Server will use a “proportional filling strategy” for file groups, thus all files within the file group will become full at the same time. This contradicts my statement about the log file not being striped. Why would I say this? The catch here is that the log file may not be placed in a file group! It’s important to understand that transaction log files are separate files and MUST be treated as such, which is why they can’t be placed in file groups.

So, how does SQL Server determine which log file to write to first? When does SQL Server decide to go to the next log file? What happens when it writes to the last log file? SQL Server will write to the first active log until it is full. When it reaches the end of the log it will go to the next file. If the last file is filled, then SQL will go to the first file and restart the sequential process; assuming there is free space available in the first log file. If there is no free space available, then SQL will try the next file and so on. If no files have free space, and auto-grow is disabled, then SQL will return the 9002 error. This log filling approach is known as a “fill-and-go strategy”.

How do I grow the log file?

As mentioned earlier, the default setting for transaction logs is to have auto-growth enabled and if push comes to shove SQL Server will increase the log for you…however; this is less than optimal approach and generally recommended as a fail-safe option. I’ll cover optimal log setup and different strategies in a future article. For the remainder of this article I’ll focus on how to grow the log and when you should do this.

To manually increase the log file size you will need to issue an ALTER DATABASE command with the MODIFY FILE option.

Example:

--The following is the T-SQL to create a new database called “MyDB”
--This database is stored in a folder called “Databases” on the local “D:” drive
--The database file is “MyDB.mdf”, the initial size of the file is 3 MB (3072 KB)
--The transaction log file is “MyDB_log.ldf”, the initial size of the file is 1 MB (1024 KB)
--NOTE: I’ve changed the FILEGROWTH of the log to a set size; this setting’s default is 10%
 CREATE DATABASE [MyDB] ON PRIMARY
 ( NAME = N'MyDB',FILENAME = N'D:\Databases\MyDB.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB )
  LOG ON
 ( NAME = N'MyDB_log',FILENAME = N'D:\Databases\MyDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB);
 GO
--The following is T-SQL to grow the transaction log file from 1 MB to 20 MB
--In practice you would set this to the optimal size based on your expected transactional usages
 ALTER DATABASE MyDB
 MODIFY FILE
    (NAME = MyDB_log,
     SIZE = 20MB);
 GO

When should I grow the log?

This is one of those age old answers you’ll hear throughout your DBA life, “it depends”. There are some basic things to consider:

1. Does your log run at near capacity between log backups?
2.  Are you expecting a large number of transactions to transpire in the near future?
3. Are you merging databases or tables from another database?

If you answered yes to any of these then a transaction log growth could be benificial to you. If you’re running at near capacity then you run a high-risk that any abnormal data transaction spikes will cause SQL Server to either auto-grow the log or return a 9002 error code if the log gets full. If you are expecting a large number of transactions to transpire soon, then you will want to calculate about how much data will be flowing in and determine if you currently have enough log space. If you’re merging databases, then you’ll want to determine what method you’re using to merge them. Example, if you merge them using bulk-logging then your transaction log will take a minimal loading; but if you are taking a table with 2 billion records and inserting through a series of T-SQL statements then you can expect a lot of transactions will be logged (and maybe should think of another method that is more reliable and less resource intensive).

How do I find out how much space is used in my log?

I’ll leave you with a final tip that will improve your ability to manage the logs. There are many different ways and information you can get in regards to your transaction log; they can range from very simple data usage stats to complex details of transactions that have transpired in your database.

This article points out that situations where your transaction log is near full capacity is an possible indication that transaction log growth may be needed; there are situations, such as in-frequent log backups that also may cause this behavior. In either case the knowledge of your log size and capacity can help in determining what actions may need to be taken. You can very easily and quickly get a listing of all the database log files in your SQL Server instance, including the file size and amount of space inside the log that has been used up. You will want to use the following T-SQL statement to view this information:

 DBCC SQLPERF (LOGSPACE)

Conclusion

While most focus in discussion groups and forums about transaction logs is covering the ability and techniques to shrink the transaction log file, there are times that you may find the opposite is needed. By taking a little caution, following some very simple guidelines, and being a proactive DBA you will find that the transaction log can be utilized to improve your SQL Server performance and ability to recover in a tight situation!