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,
- BEGIN TRAN ---->> An entry made to Log cache on the Begin transaction
- 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.
- COMMIT ------>> commit record to log record in cache and the lock released on the data page.
- 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.