Introduction to the Transaction Log

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 James Rea » Introduction to the Transaction Log

65 posts, Page 1 of 7

1 2 3 4 5 »»»

Introduction to the Transaction Log

Rate Topic

Display Mode

Topic Options

Author

Message

James_DBA

Posted Tuesday, July 01, 2008 10:41 PM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, February 16, 2013 6:31 PM
Points: 464, Visits: 8,712

Comments posted to this topic are about the item Introduction to the Transaction Log

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/

Post #527012

mladen

Posted Wednesday, July 02, 2008 3:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 23, 2009 2:33 AM
Points: 2, Visits: 67

So it is not only me getting fed up explaining, after all. Not understanding how the transaction log works seems to be a genetic defect of homo sapiens.
Here is a script to find out who is filling up the transaction log the most

create procedure sp_find_who_uses_log
as
select top 5 tdt.database_transaction_log_bytes_used,
tdt.database_transaction_begin_time,
tdt.database_transaction_state,
qt.text
from sys.dm_tran_session_transactions tst,
sys.dm_tran_database_transactions tdt,
sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
where tst.transaction_id = tdt.transaction_id
and tst.session_id = er.session_id
--and qt.dbid = 6 -- Filter by database
order by tdt.database_transaction_log_bytes_used desc

Post #527122

Nicolo Africa-400927

Posted Wednesday, July 02, 2008 4:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 5:13 AM
Points: 1, Visits: 79

Nice post. :) One small detail caught my eye. You enumerated areas where the tran log can be useful, and one of those was for "quickly importing data from old database versions to a new database version". Offhandedly, I can think of replication as a specific example of this, but might you be able to expound on some other way (if any) this can be applied? Possibly from a data migration perspective, with emphasis on using two similar, but slightly different DB versions (schemas)?

Thanks!

Post #527154

steve.lambert

Posted Wednesday, July 02, 2008 4:58 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:24 AM
Points: 72, Visits: 97

In other databases engines i have been able to translate the log to text in order to aid with fault finding. Is this possible with MS SQL Server. i.e take a copy of the log and run it up in an editor to view what has been going on.

Cheers

Steve

Post #527159

Charles Kincaid

Posted Wednesday, July 02, 2008 7:58 AM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825

steve.lambert (7/2/2008)

Not quite. SQL Server stores everything, well mostly, in pages. You can't read the data in your tables directly with a text editor and the same is true with the logs. Red Gate and Apex make some nice tools for dealing with log events.

ATB

Charles Kincaid

Post #527335

jim.powers

Posted Wednesday, July 02, 2008 8:13 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, February 04, 2013 10:00 AM
Points: 1,329, Visits: 803

Great article and thanks for taking the time to write it. I'm not necessarily "seasoned" but after six years with SQL Server, this still sheds a bit of light on that elusive file.

Post #527347

Charles Kincaid

Posted Wednesday, July 02, 2008 8:19 AM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825

Good article James. I gave you 4 on it. Please consider expanding it a bit.

Did you know that SQL Server actually writes everything to the log first and then to the .mdf? I actually had one of my servers choke, due to another process, right after I entered a cascading DELETE statement. I checked the recovery logs on restart. SQL noted that the DELETE was validated and recorded but not performed. The recovery rolled my transaction forward including enforcing the DRI down to my subordinate tables. Cool

You could add some about the different recovery models. Some about the [[[JOY]]] of trying to recover from missing log files. Not missing log backups but missing files. The poor DBA confronted one morning with the fact that they took out the development server. They had copied all the .mdf files after detaching. "Here's the new server. Just reattach these databases." "Where are the log files?" "Did you need those? We have already secured the old drive." Secured means taking it to the machine shop to be "introduced" to a drill press and sledge. I don't want to talk about it any more. It was ugly.

ATB

Charles Kincaid

Post #527358

TechnoPeasant

Posted Wednesday, July 02, 2008 8:54 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, April 18, 2013 8:14 AM
Points: 51, Visits: 71

Nice article,
Just a small history.

Transaction logs being nescessary for transaction rollback and rollforward as defined by Codd & Date in the early 1970's have been part of RDBMS technology all along.

MS jumpstarted its position in the RDBMS marketspace by co-licensing and then co-opting the Sybase v4.2 DB engine back in the mid-1980's. Its roots are still evident today in the presence of the 'master' db. MS did this because at the time, Sybase was the technology leader in part because their implementation of db cache was 100% separate (raw disk partititions) from O/S cache. By putting the DB cache under DB control, data loss is reduced and performance maximized based on the DB and not the O/S requirements which are often at odds.

Yes, I have no life..

Post #527387

James_DBA

Posted Wednesday, July 02, 2008 9:21 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, February 16, 2013 6:31 PM
Points: 464, Visits: 8,712

Charles,

Thank you for the kind comments. This article originally started based on there being so many people asking why the transaction log suddenly ballooned in size over night, then the common "how do I shrink the file..." and the "SQL Server isn't truncating my transaction log. I can tell because it isn't getting smaller in size...".... w00t

I figured that people (especially the newer DBAs) would appreciate having some sort of document that can help them to understand what is going on.

I was waiting for this article to be released and see what feedback I would get first; however, I currently do plan on expanding on this subject. In particular to discuss how the transaction log can (or won't) be a factor in database recovery models. I also have a few other "...and did you know..." that I'm going to work into there. I think this area of topic tends to be under publicized and the documentation can get to be a bit too technical at times.

Thanks,
James Rea

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/

Post #527400

James_DBA

Posted Wednesday, July 02, 2008 9:23 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, February 16, 2013 6:31 PM
Points: 464, Visits: 8,712

Bill,

Thank you for the great detailed history there! :)

Thanks,
James Rea

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/

Post #527401

« Prev Topic | Next Topic »

65 posts, Page 1 of 7

1 2 3 4 5 »»»

Permissions