Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Introduction to the Transaction Log


Introduction to the Transaction Log

Author
Message
James_DBA
James_DBA
Mr or Mrs. 500
Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)

Group: General Forum Members
Points: 548 Visits: 8754
Comments posted to this topic are about the item Introduction to the Transaction Log

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
mladen
mladen
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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



Nicolo Africa-400927
Nicolo Africa-400927
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 83
Nice post. Smile 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!
steve.lambert
steve.lambert
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 183
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
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1339 Visits: 2384
steve.lambert (7/2/2008)
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


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.

ATBCharles Kincaid
jim.powers
jim.powers
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1560 Visits: 858
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.
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1339 Visits: 2384
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.

ATBCharles Kincaid
TechnoPeasant
TechnoPeasant
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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..



James_DBA
James_DBA
Mr or Mrs. 500
Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)

Group: General Forum Members
Points: 548 Visits: 8754
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/
James_DBA
James_DBA
Mr or Mrs. 500
Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)

Group: General Forum Members
Points: 548 Visits: 8754
Bill,

Thank you for the great detailed history there! Smile

Thanks,
James Rea

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
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