Years ago I had a database that used transaction logs heavily. Under heavy load, database performance was diminished. One optimization technique you can use in this situation is to create more than one transaction log. This is a similar technique that is recommended for your tempDb today. Even if the separate transaction log files are on the same drive.
With modern computers, most of them have more than one core, and legitimately have multiple concurrent threads capable of taking advantage of multiple transaction log files. If your tempDb suffers with performance, browse for some guidance on sizing the number of transaction log files based on cores, etc.
Back to my story. In our situation we had two different drives. We decided to create transaction log files on separate drives to reduce contention and increase transactional activity. This worked like a charm. We no longer had bottlenecks during heavy transactional loads. That is, until one of the transaction log drives crashed. We had no mirror on the drives. The company had not maintained backups.
What do you do? Attach the database file without the existing transaction log file, and SQL Server automatically creates a new one. Guess what? That works fine as long as you have one transaction log. If you have more than one transaction log, it would not auto-generate them. With a support call we were able to get the database to believe it had only one log file, and then attach the database, thus auto generating a transaction log. This was a long time ago using SQL Server 2000, and I have no desire to test newer versions to see if they also have the same behavior.
The big lesson I have learned in this process is that you are better off having two transaction log files on RAID drives, than having two transaction log files on separate non-RAID drives. If you are lucky enough to have your files on a SAN the advice is still the same. The only difference is that the disks are not directly attached.
Share your SQL Server war stories. Leave a comment here, or drop a line to btaylor@sswug.org.
Cheers,
Ben