Multiple Transaction Log Physical Files – Myth punctured
I always thought that there was no really good reason for having multiple transaction log physical files.
OK, so that\’s a bit of a broad statement, but humor me a while.
The transaction log is cyclical in nature, as we all know. It\’s well documented in loads of places, not the least of which is http://msdn.microsoft.com/en-gb/library/ms179355(v=sql.105).aspx
There\’s a common understanding in the wrap-around nature of the transaction log, in that when one Virtual Log File (VLF) has no free space, the database engine will go a\’huntin\’ for a VLF that’s next in line, and will go back to the first VLF once transactions have been flushed from it. Hence \’wrap-around\’.
It follows, therefore, that when you have multiple transaction log files, then the first one will fill up with VLFs until it hits a buffer (be that disk space or auto-growth limits), and the the log will then spill into the next available physical file.
This is actually NOT the case. I know, because I\’ve just seen it.
The scenario was thus:
We\’re creating a new Sharepoint Content Database, and then doing a stsadm -o mergecontentdbs to shift the content across to the new database. A side issue to this is that the new content DB have multiple files in the PRIMARY filegroup, but that\’s for another time.
As usual, there\’s a lack of disk space on the drives that we want to use, and the current content is such that the whole database move operation is contained in a single transaction (again, one for another time, I think).
So, being a diligent DBA, I go hunting for space on another drive to hold the second transaction log file, taking the spill once the primary one hits the 35GB cap I put on it to ensure that there\’s room on the disk for the new content DB in it\’s entirety. I find some space on another drive, once I\’ve deleted a developer\’s PST backups and old Service Pack installers (), so that\’s where I create my second physical log file. I fully expected this to remain at the size I created it until it was required to take up the spill.
What I ACTUALLY saw is that from the moment I created the secondary log file, it started being used even though the primary was yet to hit the buffers. Odd, I thought. OK, so maybe it\’ll just use that one until it fills up and go back to the primary.
Wrong again.
It seems that what the database engine is doing is using one physical file until it hits the next auto-growth point, then it switches to the secondary. While using the secondary, it then expands the primary by the auto-growth increment. When the secondary hits the next boundary, transactions revert to the primary in the same way as before, so you get this flip / flopping of transaction log file usage, and growth rates are split 50/50 between the two physical files.
Of course, once the operation is complete, I\’ll remove the secondary log file, as I don\’t need it, and it\’s on a drive it shouldn\’t be on, but the observed behaviour certainly seems at odds with the accepted wisdom that one will fill, and then spill to the second.
See, you really do learn something every day.
Back soon…
The Modern Dataist 