Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question about the transaction log Expand / Collapse
Author
Message
Posted Friday, February 14, 2014 11:16 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 4:28 PM
Points: 115, Visits: 402
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?





Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1541688
Posted Friday, February 14, 2014 12:29 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1541710
Posted Friday, February 14, 2014 12:30 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:20 AM
Points: 261, 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?
Post #1541711
Posted Friday, February 14, 2014 12:42 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1541722
Posted Friday, February 14, 2014 12:56 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 4:28 PM
Points: 115, Visits: 402
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

Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1541729
Posted Friday, February 14, 2014 1:07 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 4:28 PM
Points: 115, Visits: 402
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
....


Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1541736
Posted Friday, February 14, 2014 1:47 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:20 AM
Points: 261, 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.
Post #1541749
Posted Friday, February 14, 2014 1:52 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1541755
Posted Friday, February 14, 2014 1:55 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1541759
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse