▼

Thursday, 16 August 2012

What happens in SQL Memory during a logged operation


Ever thought about what are a step by step task happens during a logged transaction in SQL memory, This is what i found out from a technet article for an INSERT Query,
  1. BEGIN TRAN ---->> An entry made to Log cache on the Begin transaction
  2. INSERT ------>> The data page affected is moved to buffer cache/data cache,lock aquired, Record sent to Log cache and the modification done to the data page in cache.
  3. COMMIT ------>> commit record to log record in cache and the lock released on the data page.
  4. CHECKPOINT / LazyWriter ----->> Moves the modifed data page to disk.  Eager Writer --->> Moves all non-logged and Bulk-logged transaction pages from data cache to disk.
At the time a modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.
at

1 comment:

  1. alisha4 March 2026 at 23:13

    This is a very clear and helpful breakdown of what happens in SQL memory during a logged operation.. I really appreciate how you explained each stage, from BEGIN TRAN to COMMIT and CHECKPOINT—in a simple, step-by-step way.. Understanding how log cache, buffer cache and dirty pages interact makes it much easier to grasp why SQL Server enforces write-ahead logging. The explanation about log records being written to disk before dirty pages are flushed is especially important for understanding data integrity and crash recovery. As someone who enjoys technical topics and also practices structured explanations through write for us content writing platforms, I find posts like this extremely valuable for reinforcing both conceptual clarity and practical database knowledge.

    ReplyDelete