December 30, 2011 at 4:14 am
Hi,
I was wondering if someone could just clarify something for me.
When an insert/update or delete is written, as I understand this change is;
1. The change is written to the transaction log.
2. At the same time the change is made to memory.
So at this point we can ensure that we can apply the change if a restart occurs.
Then at a given point the CHECKPOINT or Lazy Writer will come along and write the changes from memory to the data file, the lazy writer will then free up the dirty pages from the memory as well.
Is that right? I know it's at a high level, I'm just trying to make it clear in my head again.
Thanks,
Nic
December 30, 2011 at 4:18 am
Pretty much.
The order is:
Change written to memory and log entry written into log buffer.
Log buffer flushed to disk when the transaction commits (or sometimes earlier)
Later the data change is written from memory to the data file by the lazy writer or checkpoint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2011 at 4:22 am
I knew I forgot something, the log buffer.
Thanks for clearing that up for me Gail, we were talking about CHECKPOINT and I got myself confused (again).
Enjoy the new year.
Nic
December 30, 2011 at 6:19 am
Also maybe worth noting that both the log write (when the log buffer is flushed to disk) and the data write (via checkpoint/lazywriter) can occur before the transaction commits, however it still must be log first, then data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy