Stairway to Transaction Log Management in SQL Server, Level 1: Transaction Log Overview

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Tony Davis » Stairway to Transaction Log Management in SQL...

13 posts, Page 1 of 2

1 2 »»

Stairway to Transaction Log Management in SQL Server, Level 1: Transaction Log Overview

Rate Topic

Display Mode

Topic Options

Author

Message

Tony Davis

Posted Thursday, June 02, 2011 8:54 AM

Mr or Mrs. 500

Group: Administrators
Last Login: Yesterday @ 9:24 AM
Points: 511, Visits: 948

Comments posted to this topic are about the item Stairway to Transaction Log Management in SQL Server, Level 1: Transaction Log Overview

Post #1118800

Vineet Khanna

Posted Friday, June 17, 2011 12:37 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, June 08, 2013 4:15 AM
Points: 45, Visits: 236

Thanks Tony for sharing this and looking forward to this discussion....
I am actually working on a project where have to enable Tlog backups on one of my system.The system is quite heavily used so have to first gather information on what average per day t log size can get to so as to ensure we have enough disk space to accommodate it comfortably and even little bit worried on performance impact as well but then point in time recovery is only available when we enable this option so don't have any choice really....

Post #1127102

Festeron

Posted Friday, June 17, 2011 8:30 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 9:48 AM
Points: 142, Visits: 172

Thanks for starting this series, Tony. It will be good to have a complete transaction log reference

Could you clarify this:

Data is modified in the data cache, and the log records to describe the effects of the transaction are created in the log cache. When a transaction is committed, the log records are written to the transaction log, on disk.

I tried running your million row insert within a transaction, and even before the commit the log was at 99.74MB and 93.46% full. This makes sense because the transaction creates such memory pressure that it couldn't be contained by RAM. The log records must be written as the transaction occurs, not at commit time as you claim.

How do log records "describe the effects of the transaction"?

Also, I'd like to see more about how a checkpointed but uncommitted transaction gets rolled back. I always thought that the log contained both before and after images of the row, but you talk about "reversing entries" being generated. I can see how a delete can reverse an insert, but how can an update reverse an update without knowing the old values?

Good luck with the stairway

Post #1127399

Steve Jones - SSC Editor

Posted Friday, June 17, 2011 10:25 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:19 PM
Points: 31,526, Visits: 13,863

checkpoints flush log records to disk, both committed and uncommitted. When the database recovers (in restore or on restart), the redo/undo actions either mark the data files with the changes or remove the changes from the data files, depending on whether the transaction was committed.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1127503

Festeron

Posted Friday, June 17, 2011 12:14 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 9:48 AM
Points: 142, Visits: 172

Agreed. I think that's all included in the article.

But specifically, when the transaction that has made row changes is checkpointed and not committed, and for whatever reason needs to roll back, where does the old version of the row come from? Not the data file - it was updated at checkpoint. Tony said it was a reversing entry - but how is that generated?

If I change a salary from 53 to 57, and the change is checkpointed, is the data file updated? Yes.
Is the change recorded in the log? Of course.

But where is the 53 preserved? We need to keep that value somewhere in case the transaction [which is still running] decides to roll back. I thought it was in the before image of the row recorded in the log, but if I'm wrong about this I need to know.

Post #1127595

Steve Jones - SSC Editor

Posted Friday, June 17, 2011 2:22 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:19 PM
Points: 31,526, Visits: 13,863

I believe the before data is included in the log record. Everything needed for roll forward / roll back is in the log, so it must either be a linked record or the same record

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1127668

James Stephens

Posted Saturday, June 18, 2011 8:45 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 9:52 PM
Points: 28, Visits: 169

Hi all,
I have a related question that I think was somehow answered in the above posts but I just wanted clarification for academic purposes:

Suppose I change a field of data from Table-1 from "Some Data Before" to "Some Data After".
Now suppose that this is not committed but due to memory pressure or some other reason there's a checkpoint and it's written from the log cache to the disk (.Ldf), and also from data cache to the disk (.mdf).

Now I want to do a Select with "read uncommitted" (I'm not fully sure but I think this is done using "With (NoLock)"??).
From where does my Select statement fetch the "Some Data Before"? Does it, seeing the With(Nolock), divert the request to the .ldf data and step back to the last committed value of that particular row?
Thanks,
Jim

Post #1127756

codebyo

Posted Saturday, June 18, 2011 3:28 PM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 8:50 AM
Points: 862, Visits: 1,440

Amazing read. Thanks for the contribution.
Eagerly looking forward to the next chapters (levels).

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2

Post #1127799

Festeron

Posted Sunday, June 19, 2011 7:40 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 9:48 AM
Points: 142, Visits: 172

James Stephens (6/18/2011)

Memory pressure doesn't enter into when a checkpoint is done. It's happens automatically only when the Checkpoint Handler estimates that there is too much uncheckpointed information in the log, and recovering it would take longer than recovery_interval minutes.

and it's written from the log cache to the disk (.Ldf),

No, this happens far more often than at checkpoint time. The article asserts that the .LDF is written to at commit time, but my question about the million rows shows that it happens before then.

and also from data cache to the disk (.mdf).

This is correct. Dirty pages in the data cache, committed or not, are written to the .MDF

Now I want to do a Select with "read uncommitted" (I'm not fully sure but I think this is done using "With (NoLock)"??).

No, read uncommitted means "I will ignore the exclusive locks of other users as I do my reads, and read the uncommitted changes they have made to the row". Also known as a Dirty Read, there is the risk of reading something that will soon be rolled back by the other user.

You can enable it with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Not that I'm saying this is a good idea.

From where does my Select statement fetch the "Some Data Before"? Does it, seeing the With(Nolock), divert the request to the .ldf data and step back to the last committed value of that particular row?

Quick rule: Unless you are in the middle of recovery, or some other unusual process, the log is not read. In particular, a select statement will never read the log.

Your select statement, like any other, will first look for the rows you want in cache. If it finds it there, all's good - if not, the page of rows is read from disk.

Notice that you doing a select is very different from someone else doing a select on the changes you've made. Your connection can see all the changes you have made, committed or not. Other users can [ordinarily] see only the changes you have committed. Unless, of course, they have turned on READ UNCOMMITTED, which they shouldn't do.

For more complexity, look in Books Online for Snapshot Isolation.

Post #1127949

James Stephens

Posted Monday, June 20, 2011 11:06 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 9:52 PM
Points: 28, Visits: 169

Festerson,
Thanks, that was definitely enlightening. I read through the bol stuff, and realized I had mis-used a term in my question.

What I was wanting to know was really: When using read-Committed (instead of read-UNcommitted), if the case is that un-committed data can be written from the cache to the .mdf, then where is the previous committed value stored?

I understand that with the new Snapshot isolation that tempdb is where the different row versions are, but without snapshot isolation on--and one is reading data using the default read-committed--where are previous versions stored? Would this just exist in the data cache then as a 'row version' there--the same idea as the Snapshot isolation but not using Tempdb and just doing that same mechanism in the cache? Again, this is purely academic so I can get a clear, virtual picture of what's happening with the data in these little slices of time.

If my assumption is correct then it makes sense then that if a crash happened just at that moment that the .mdf's disk value of that row would be rolled back during recovery because that transaction was known not to be committed as recovery reads the .ldf.
--Jim

Post #1128410

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions