A few days ago Kendra Little did a great Podcast about Write Ahead Logging in SQL Server. Key point is that all transactions are written to the transaction log first and then to the data file. This enables SQL server to fulfil the Durability requirement (one of the ACID properties of transactions). Kendra did a good job of explaining but I didn’t get her right the first time and did some research myself on roll backs and roll forwards.

The question I started with is the following: If SQL Server has got only valid committed transactions in the data file, why would it need to do something like a rollback or undo operation when the database starts up (crash recovery) or before performing CHECKDB?

I typically see the following messages in the SQL Server Error Log:

“10 transactions rolled forward in database ….” and “10 transactions rolled back in database …”

Well it turns out, that my original thinking is not exactly the way it works. No charges at Kendra…she explains it properly in her podcast but somehow I didn’t understand it at first. I started reading “SQL Server Transaction Log Management” by Tony Davis and Gail Shaw. That’s a great free eBook presented by Redgate. In Chapter 1 (page 16 onwards) the answer is right there

• When one transaction issues a COMMIT, SQL server flushes all of the log records from RAM to the transaction log file on disk. That comprises log records of different transactions, which aren’t committed yet as well.
• The database checkpoint process regularly flushes dirty pages (aka pages in RAM (buffer pool) whose data have been modified and differ from the content on disk) from RAM to disk (precisely the data file). Thus it controls the work SQL server has to do after a crash occurs. Again, SQL Server is not very specific about the dirty pages it writes out to disk….it just takes all of them regardless to which transaction (uncommitted or committed) they belong to.

Explanation taken from the book “SQL Server Transaction Log Management” and slightly rephrased

Well, it makes sense that SQL Server should have to do as little work as possible after a crash in rolling forward transactions and making changes to the data file to reflect already successfully committed transactions.

However it took me some while to understand the reason why SQL Server could write dirty pages belonging to an uncommitted transaction to the data file. If the server crashes and the transaction does not commit, it would need to undo (or roll back) these changes after restart. Luckily I was on training last week and got the chance to ask Willfried Färber, a former SQL Server MVP, about it. He told me that the chance SQL server would have to do a rollback on any transaction is much lower than the probability this transaction successfully commits. Therefore SQL server flushes dirty pages to disk whatever the status of the transaction is. I suppose that could help IO performance if you are running long transactions (which is not exactly best practice…at least in an OLTP environment 🙂 ). Probably the reason for this behaviour dates back to the early days of SQL Server where storage was much slower.

Thank you very much for reading. I am still learning about this topic and am by no means an expert. If you spot an error in my writing please let me know.