February 14, 2014 at 11:16 am
I have a few questions about the different processes involved in writing to the transaction log file:
1-Is there any other process writing to the Log file other than
1-checkpoints
2-transactions getting committed
3-Lazy writer ?
2- As soon as a transactions gets committed it's data are written from the buffer pool (Log cache) to the log file , but could the dirty pages for a long running transaction get flushed to the log file before the transaction gets committed assuming neither a checkpoint nor a lazy writer has been applied at the time?
Pooyan
February 14, 2014 at 12:29 pm
pooyan_pdm (2/14/2014)
1-Is there any other process writing to the Log file other than1-checkpoints
2-transactions getting committed
3-Lazy writer ?
Yes. Think about what other processes use the log for any reason.
2- As soon as a transactions gets committed it's data are written from the buffer pool (Log cache) to the log file , but could the dirty pages for a long running transaction get flushed to the log file before the transaction gets committed assuming neither a checkpoint nor a lazy writer has been applied at the time?
Dirty pages don't go to the log file. Dirty pages are data pages and go to the data file. The dirty data pages for a transaction can be written to the data file at any time before or after the transaction commits, by the lazy writer or checkpoint process.
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
February 14, 2014 at 12:30 pm
It depends. Do you have any sort of Replication going on? Then the logreader agent will be marking transactions when they are moved to the distribution database.
The main source of writes on a transaction log are changes to database pages (not just on the commit, by the way). If you are updating 10 million rows, and the update takes 10 minutes, then the log entries (before and after pages) are written to the transaction log all throughout the 10 minutes. if the server is restarted in the middle of the 10 minutes, then none of the update records in the log will be marked as committed, and any applicable changes will be rolled back. If the server is restarted after the commit is written to the log, then the after images will be rolled forward.
Make sense?
February 14, 2014 at 12:42 pm
Matt Crowley (2/14/2014)
If you are updating 10 million rows, and the update takes 10 minutes, then the log entries (before and after pages) are written to the transaction log all throughout the 10 minutes.
Yes and no. The log records containing the before and after rows or row portions are written to the log buffer all throughout the 10 minutes and to disk when the log buffer flushes either because it's full or because some other transaction has committed or any of the dirty data pages need to be written to disk.
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
February 14, 2014 at 12:56 pm
I agree with you on that , cause other wise there could be a big hit on a log file every time a long running transaction get's committed. the reason I asked was that I couldn't find the accurate explanation on the web. Thanks
Pooyan
February 14, 2014 at 1:07 pm
The final destination of the dirty pages is the data files but they should be pushed to the log file before or at the same time they are being pushed to the data files. Am I missing something here?
another question : so what makes the Log cache to be flushed to the log file?
1-whenever it gets full
2- when any transaction gets committed
3-check point
4- lazy writer
....
Pooyan
February 14, 2014 at 1:47 pm
A record of what has been updated is pushed to the log., not the actual pages.
Due to the importance of the log data (for recovery), the log writes are synchronous, so there should be very little if any delay in the recording of the log entries. Only the data pages are written to disk asynchronously.
February 14, 2014 at 1:52 pm
pooyan_pdm (2/14/2014)
The final destination of the dirty pages is the data files but they should be pushed to the log file before or at the same time they are being pushed to the data files.
Data pages do not get written to the log. Data pages get written to the data file. Log entries get written to the transaction log.
so what makes the Log cache to be flushed to the log file?
Already answered that one
log buffer flushes either because it's full or because some other transaction has committed or any of the dirty data pages need to be written to disk.
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
February 14, 2014 at 1:55 pm
Matt Crowley (2/14/2014)
Due to the importance of the log data (for recovery), the log writes are synchronous, so there should be very little if any delay in the recording of the log entries.
Synchronous, but they don't have to be written to disk the instant they're generated (if they did, there wouldn't be a log buffer)
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply