Transaction Log Size

One of the most common points of confusion that I run into with new DBAs on SQL Server is transaction log maintenance. I want to touch on one area of log maintenance here in this post: how to maintain the size of the transaction log. In other words, how to avoid the runaway transaction log growth that many DBAs experience and suffer from in SQL Server.

There is so much information (and some misinformation) on SQL blogs about the transaction log maintenance. What I’m going to do here is to ask you to rely on MSDN or TechNet books online as your source and to read-up on VLF’s in the transaction log. Because as the transaction log’s virtual log files grow and are being used by the SQL writer processes, the transaction log will need to grow so that the database engine can keep-up with your transactions.

Before you can think about log file shrinking, you need to first clear the status flag on the VLFs by running a CHECKPOINT or Log Backup, and then you can shrink your log file. If your database is a production tier-1 application database, then make sure you are running in full recovery model and backing up the logs regularly. But keep in mind that your transaction log will, and should, grow to the size that it needs in order to write pages to disk.

It is not really plausible to create generalized stake-in-the-ground guidelines in terms of recommended log sizes or VLF guidance. Instead, what I am going to do is to recommend you take a few minutes to read these 2 blog posts by Kimberly Tripp that should be any new SQL Server DBA mandatory reading about logs & VLFs: here and here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s