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 12345»»»

Introduction to the Transaction Log Expand / Collapse
Author
Message
Posted Tuesday, July 01, 2008 10:41 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
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
Posted Wednesday, July 02, 2008 3:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, July 02, 2008 4:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 6:16 PM
Points: 1, Visits: 82
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
Posted Wednesday, July 02, 2008 4:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 21, 2014 8:34 AM
Points: 86, Visits: 122
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
Posted Wednesday, July 02, 2008 7:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 06, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid

Post #527335
Posted Wednesday, July 02, 2008 8:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 21, 2013 2:59 PM
Points: 1,329, Visits: 807
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
Posted Wednesday, July 02, 2008 8:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 06, 2014 12:59 PM
Points: 801, Visits: 1,962
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.

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
Posted Wednesday, July 02, 2008 8:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Wednesday, July 02, 2008 9:21 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
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...".... 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
Posted Wednesday, July 02, 2008 9:23 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse