I was very surprised to see the results of my latest poll, which asked “When was the last time you tuned your transaction logs?”
According to those DBAs who responded to the poll, about 60% of them said they didn’t know transaction logs could be tuned. And just over 19% said they have never tuned their transaction logs. Sadly, only about 21% said they have tuned their transaction logs in the past.
If you are one of those DBAs who don’t know transaction logs can be tuned, you need to take the time as soon as possible to learn about this topic.Why? First, every data modification made in SQL Server must be logged. Second, no transaction can complete until all of the related data modification are written to the transaction log. And third, writing to a transaction log is a single thread process. On a busy SQL Server instance, writing to the transaction log can become a bottleneck, preventing transactions from being completed on a timely basis, slowing down the entire instance.
There are many ways to tune a transaction log, some very simple, and others slightly more complicated, but it is not beyond the ability of most DBAs. To learn more about transaction log performance tuning, download my presentation slides on “Inside the SQL Server Transaction Log”, which will give you a good start on learning the basics of this important topic.