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

Question

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

Tony Davis

SSCarpal Tunnel

Points: 4405
More actions
June 2, 2011 at 8:54 am

#251102

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Vineet Khanna SSC Journeyman Points: 91 More actions · Answer 1

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....

Festeron SSC Eights! Points: 927 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 3

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.

Festeron SSC Eights! Points: 927 More actions · Answer 4

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.

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 5

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

James Stephens Mr or Mrs. 500 Points: 517 More actions · Answer 6

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

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 7

Amazing read. Thanks for the contribution.

Eagerly looking forward to the next chapters (levels).

Best regards,

Andre Guerreiro Neto

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

Festeron SSC Eights! Points: 927 More actions · Answer 8

James Stephens (6/18/2011)
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

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.

James Stephens Mr or Mrs. 500 Points: 517 More actions · Answer 9

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

Jagadish Kumar Punnapu SSChasing Mays Points: 645 More actions · Answer 10

Jagadish Kumar Punnapu

SSChasing Mays

Points: 645

August 17, 2011 at 4:52 am

#1369497

Thanks Tony... Good Article...

nicolas.pages SSC-Addicted Points: 430 More actions · Answer 11

Hi,

I have a basic question for which I can't manage to find a certain and final answer.

When a transaction is "committed", what exactly does that mean in the log?

Is there an "OK" or "COMMITTED" written after the log records?

Is is just the end time?

Thanks,

Nicolas

bsantosh6 SSC Enthusiast Points: 190 More actions · Answer 12

Thank you Tony! That was extremely useful ! A printout of this will be with me till I work on SQL Databases....:)

With teachers like you,I could be a rocket scientist!