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 - once 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.



Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.