Multiple Transaction Logs in SQL Server again…

Got yelled at today by Jonathan Kehayias, a slammin’ SQL dude and Microsoft MVP, about my blog on transaction logs and multiple files that I did back a few weeks ago. He brings up a good point in that my conclusion has something that I need to clarify and change in my posting: If you have more than one transaction log file it will be written to during the course of a transaction, and my verbiage said that they would not. Wrongo! Thanks for pointing this out to me…that one slipped by me. Go no further than any of the references I had linked in the blog, or better yet just go to MSDN, for great reading on the transaction log file. I quote Microsoft directly from Books Online…

If the log contains multiple physical log files, the logical log will move through all the physical log files before it wraps back to the start of the first physical log file.

This is easy to see in a simple example.  Create a database and add two log files.  Uncheck the Enable AutoGrowth first two files and set their maximum file size and restricted file growth to 1 or 2MB, and for the third leave this set to unlimited growth with Maximum File Size of unrestricted. Start a transaction and watch all of them fill – the third will keep growing as long as your script keeps doing DML while the others max out to the file size that you set, naturally.  

We should be clear on this now I think. Boooyaaaaaaaaaaash!

I would like to also reiterate and emphasize one thing while I’m on the line if I may (and I will take liberty since I am the sole proprietor of this blog), and that is, if you have two, five, or a hundred log files within a database they will not be written to simultaneously, the point of my blog post in the first place. Log files in SQL Server are written to, but not in parallel. So my point about no increased throughput gained by multiple log files is a valid one. The client where I was stationed added multiple data files in their user and tempdb databases, their DBAs doing so because of best practice. I assumed that since what they read for data files must hold true for log files, they’d go ahead and think that the more log files the merrier, possibly to obtain some sort of speed on log writes or whatever I have no idea.  Additional log files do nothing other than let the DBA on-call sleep a little better at night since he or she now has a failsafe should the log file become full, which I also mentioned in the original post.

I think we’re gold now. I hope.

Thanks much,

Lee Everest

-----------------------------

I actually checked three times that I spelled his name correctly.