SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question about the transaction log


Question about the transaction log

Author
Message
pooyan_pdm
pooyan_pdm
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 422
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88845 Visits: 45284
pooyan_pdm (2/14/2014)
1-Is there any other process writing to the Log file other than
1-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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Matt Crowley
Matt Crowley
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 469
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88845 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


pooyan_pdm
pooyan_pdm
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 422
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
pooyan_pdm
pooyan_pdm
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 422
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
Matt Crowley
Matt Crowley
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 469
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88845 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88845 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search