April 9, 2021 at 1:41 pm
Hi All
I hope to get some clarity on a confusing topic for me.
With WAL - Any modifications are written to the transaction log before they're written to the data files (mdf). Is this correct?
I'm looking for a very simple explanation to clear this up for me.
Most articles mention something along these lines...
which guarantees that no data modifications are written to disk before the associated log record is written to disk.
The "no data modifications" means data files?
I often see "flushed to disk" mentioned. What exactly does this mean?? Flushed to ldf on disk or mdf on disk?
Where does the log cache part of the buffer pool fit in here?
How does delayed durability fit in here?
Thanks
April 9, 2021 at 3:34 pm
First, need to number these qs:
(1) With WAL - Any modifications are written to the transaction log before they're written to the data files (mdf). Is this correct?
Most articles mention something along these lines...
which guarantees that no data modifications are written to disk before the associated log record is written to disk.(2) The "no data modifications" means data files?
(3) I often see "flushed to disk" mentioned. What exactly does this mean??
(4) Flushed to ldf on disk or mdf on disk?
(5) Where does the log cache part of the buffer pool fit in here?
(6) How does delayed durability fit in here?
Now everyone has a common point of reference to respond. People may want to weigh in on some topics but not others.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 9, 2021 at 3:46 pm
(1) Yes.
(2) It means to any recoverable resource, that is, any resource affected by COMMIT / ROLLBACK.
(3) Data in memory (in a buffer) that is forcibly written to disk is said to be "flushed". This does not mean any type of problem/error has occurred. SQL Server has background processes that flush data to disk.
(4) Flushed applies to any data written.
(5) A log cache is the log equivalent of a data buffer/cache. Since log writes are so critical, they are given separate buffers/caches to speed them up.
(6) Delayed durability gives SQL "permission" to write data before the corresponding log write(s) has(have) been confirmed. It can speed up processing, but at the possible risk of permanently losing data.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 14, 2021 at 3:32 pm
Thanks
(3) Data in memory (in a buffer) that is forcibly written to disk is said to be "flushed". This does not mean any type of problem/error has occurred. SQL Server has background processes that flush data to disk.
Like Checkpoints?
(6) Delayed durability gives SQL "permission" to write data before the corresponding log write(s) has(have) been confirmed. It can speed up processing, but at the possible risk of permanently losing data.
So, delayed durability has nothing to do with the log cache?
So, SQL will write to mdf's before writing to ldf's?
I realize that something like this is not exactly day-to-day DBA operational work, I would just like to get a complete understanding.
I'm trying to visualize this whole process and it's really difficult.
Thanks
April 14, 2021 at 3:46 pm
Thanks
ScottPletcher wrote:(3) Data in memory (in a buffer) that is forcibly written to disk is said to be "flushed". This does not mean any type of problem/error has occurred. SQL Server has background processes that flush data to disk.
Like Checkpoints?
ScottPletcher wrote:(6) Delayed durability gives SQL "permission" to write data before the corresponding log write(s) has(have) been confirmed. It can speed up processing, but at the possible risk of permanently losing data.
So, delayed durability has nothing to do with the log cache?
So, SQL will write to mdf's before writing to ldf's?
I realize that something like this is not exactly day-to-day DBA operational work, I would just like to get a complete understanding.
I'm trying to visualize this whole process and it's really difficult.
Thanks
(3) Yes, exactly. The point of a CHECKPOINT is to flush all data for the db to disk, creating a guaranteed recovery point.
(6) Yes. Yes, potentially. More likely it has not written either but it still allows the transaction to finish. You get more speed and throughput but at a slight risk of genuinely losing data.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 14, 2021 at 3:51 pm
(6) Yes. Yes, potentially. More likely it has not written either but it still allows the transaction to finish. You get more speed and throughput but at a slight risk of genuinely losing data.
So it's kind of a optimistic way of thinking about it. It assumes that the corresponding writes will happen sometime.
Where do all of these writes-to-happen-in-the-future pile up?
Does this not cause a backlog somewhere?
If something goes wrong and these writes don't happen, that's the data you'll lose?
April 14, 2021 at 3:58 pm
I'm trying to get a flow of exactly what happens.
Am I on the right track here.....
Thanks
April 14, 2021 at 5:34 pm
Yeah, that's basically it. Although "changes written to mdf" is "changes to mdf made in buffers". The mdf pages do NOT have to be written to disk for the trans to continue. The log can be used to rollback / forward recover that data. That's the whole point of WAL.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply