Q&A with Kalen Delaney
The Biggest Transaction Log Myth
Keeping the transaction log healthy is an essential part of managing SQL Server and protecting your data. By storing log records of transactions, SQL Server ensures that you can recover up to the point of failure—if you understand how the log works and configure and maintain it correctly. An out-of-control or mismanaged transaction log could drag down SQL Server performance, stymie data recovery, and even cost you your job.
In her Nov. 2 PASS Summit Unite 2009 pre-conference seminar, “Care and Feeding of the Transaction Log,” SQL Server MVP and internals expert Kalen Delaney will spend the day examining the transaction log, digging into log internals, looking at undocumented commands for analyzing log usage and growth, exploring physical management of log files, and demonstrating how SQL Server processes the log during BACKUP and RESTORE commands in different recovery models.
Q: Because the transaction log works differently for various operations and when SQL Server is in different recovery models, there’s a lot to learn and watch for in managing it. What’s the biggest challenge SQL Server pros face in managing the transaction log?
A: The biggest challenge seems to be just understanding what the log is and what it isn’t. Many people think the log is simply an audit trail of every operation performed, so that if we could just see the contents of the log, we would know what statements were run on our SQL Server instance, by whom, and when. That is a myth. The log is information SQL Server needs to make sure your data and your physical storage structures are consistent.
Q: When the transaction log isn’t managed correctly, what problems do you see?
A: The biggest problem is uncontrolled growth of the transaction log. I’ve seen 30GB-50GB databases with a log that is hundreds of GB in size! The second problem is not having the files you need for recovery after a failure. You might think you have backups, but if you’re not managing the log properly, the backups might be unusable or might not allow you to recover as much data as you thought you could.
Q: How do you find the undocumented commands you share for examining log usage and growth? How important is it to have these commands in your arsenal of management tools?
A: You can discover some undocumented commands by running a trace, some by reading the text of the system procedures, and others by reading Microsoft Knowledge Base articles and blogs written by members (or former members) of the SQL Server team at Microsoft. When things are going well, you usually don’t need these commands. However, some undocumented commands are invaluable when you’re troubleshooting problems. There are others I have never really needed to use. I just like them because they’re geeky.
Q: A day-long seminar on the transaction log means that there’s plenty to cover and learn in managing this SQL Server feature. What 3 things do you want attendees to take away from your seminar?
A: I want to get all the details of how the log works—and the tips and tricks for managing it—together in one place. I'd like attendees to
- Understand how SQL Server manages the log internally
- Know the difference between shrinking and truncating
- Be able to determine the best recovery model for their database once they understand how the log behaves in each of the recovery models
Kalen Delaney, principal consultant with SQLearning, Inc., is an internationally known trainer, consultant, author, and speaker, focusing on SQL Server internals, query tuning, data storage, and concurrency management. Her new book, Microsoft SQL Server 2008 Internals (Microsoft Press 2009), hit bookstores in March.