THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

A Transaction Log "Ballast" File

I've done a lot of sessions and articles about performance tuning your SQL Server environment over the years.  A common question that comes up is where and how to place the log files of a SQL Server database.  Of course, ideally, you'd place the transaction logs on their own mirrored pair of disks.  However, the follow-up question usually sounds something like "Yeah, that'd be great, but I can't afford that.  So what do you recommend?"

In a situation like this, I usually recommend that log files and the OS share a mirrored pair of disks, especially for smaller systems.  My recommendation follows that of Geoff Hiten, one of the sharpest SQL Server MVPs I know who's especially well-versed in hardware tuning.  Geoff's reasoning follows - o
nce a SQL server is up and running in a steady state, there is little activity on the OS partition. A well-tuned SQL server should not page significantly.

Geoff also introduced me to something called a "ballast" file.  Of course, ballast is the heavy stuff, usually water, held in the bottom of a ship to properly balance its weight.  With SQL Server transaction logs, a ballast file is a sort of extra, empty log file that you can "dump" (that is, easily delete) to get a few hundred megabytes back if you should ever allow your log files to grow too large.  Even better, naturally, is to cap the total growth of your transaction log files so that they cannot consume all available space on a disk array.

Cheers!

-Kevin

Published Monday, January 08, 2007 4:28 PM by KKline
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement