SQL Server Storage Internals Part 1 - Basics

[ERR!]
added by jacobsebastian
8/18/2011 9:18:22 AM

170 Views

While learning query tuning topics, I got a chance to understand anatomy of data page and different types and levels of Index pages. This learning helped me to understand concepts better and to give better performance tuning solutions later. This is my...

Read the full story
Database
2 comments
vijayst
8/18/2011 9:04:13 PM
It is interesting to know the internals of SQL Server. I want to know how the transaction log works - for INSERT, UPDATE, DELETE statements.

I also like to know how transactions are coordinated. I am aware of two-phase commits. There is a PREPARE and COMMIT phase. But, I do not know what really happens in the PREPARE phase, and what happens in a COMMIT phase. If a COMMIT phase fails because of a hardware error, what really happens to the entire transaction?
dpeterson
8/19/2011 5:42:40 PM
If there is some sort of hardware failure during the commit phase, it's possible that the database could be left in an inconsistent state. However, depending on the recovery level setting of the database, it might be possible to reallocate the damaged pages (if it's not a total failure) and possibly replay the transaction.
I think the answer is "it depends" because it depends on what fails, when it fails, and what the recovery settings are on the database.