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 Wednesday, July 2, 2008 10:06 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:00 AM
Points: 473, Visits: 8,737
Hi,

This is a quick update to a major typo in the article.

In the section titled "Shrinking the Log"; I typed in that the command FILESHRINK is used to shrink the file. In actuallity the proper command is SHRINKFILE. You can view the proper command, syntax, and description on MSDN at: http://msdn.microsoft.com/en-us/library/ms189493.aspx. You can also find the link at the end of the article in the section titled "Additional Resources".

I apologize for this typo and any others you may find.

Thank you,
James Rea


~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
Post #527425
Posted Wednesday, July 2, 2008 10:33 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: 2 days ago @ 12:02 PM
Points: 438, Visits: 402
I see someone already mentioned that the transaction log is a write-ahead log, which, IMO, is its most important distinguishing feature. The transaction log is a core operational strategy employed by SQL Server. It is not a record of "what happened", not an added feature, and not in the slightest optional.

I think that most people who complain about ballooing log files are confused by the term "log." Let me try some analogies here.

A paper check register is a type of log. You write a check, then record it in the register (don't you hate the people who hold up the line doing this?). But, you might forget to do that. You might also log the wrong amount. And the check doesn't suffer for it--it's still paid as you wrote it. Your check register is only valid if you are accurate and diligent. Not only that, but your bank couldn't care a bit if your register is wrong--it has no bearing.

That's a write-after log. Take action, record it. And it's what most people think of when you say "log."

Now, suppose that you don't write checks by hand, you only print them from a finance program. First, you enter into the program all the details about the check. Then, the program uses those details to print the check. The record of the check in that program is a write-ahead log. The check can't exist without that log entry, and the log entry was used to create the actual check.

SQL Server uses a write-ahead transaction log. A committed transaction goes to the log FIRST. The lazy writer uses the LOG as its source of modifications to make to the actual database files at the next checkpoint. Before that checkpoint occurs, your committed data exists only in the log. Therefore, the log is always accurate and complete because the data wouldn't be there without it.

And, I have to take issue with the MS Access origin of the transaction log. I was working with SQL Server 6.0 at the same time as MS Access 2.0 was hot. SQL Server has used a write-ahead log for its core operation for longer than MS Access has existed! I think any feature copying was the other way around.
Post #527435
Posted Wednesday, July 2, 2008 10:56 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: Yesterday @ 11:42 AM
Points: 821, Visits: 2,028
Stephanie

Your analogy is valid, correct, and well stated. It is possible to turn it off, however. Set recovery mode to simple and nothing gets logged. In that sense it is optional. It's not wise to do that under most circumstances.


ATB

Charles Kincaid

Post #527448
Posted Wednesday, July 2, 2008 12:01 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 3,221, Visits: 2,375
Excellent article James.

It should be required reading as entry criteria to join SSC !




Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #527492
Posted Wednesday, July 2, 2008 12:26 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: 2 days ago @ 12:02 PM
Points: 438, Visits: 402
Charles Kincaid (7/2/2008)It is possible to turn it off, however. Set recovery mode to simple and nothing gets logged. In that sense it is optional.


Um... no, sorry, it doesn't work that way.

You cannot write anything to the database without it being logged FIRST. Maybe there are some exceptions in bulk operations, but that's more a QOTD topic.

I mentioned checkpoints in my previous post, and that's the key to simple recovery mode. When you run a statement that modifies the database, the actions you take are written to the log immediately upon commit (or even before? I think sometimes before commit). The log file writes are immediately flushed to disk. (A good reason to give a large database a separate drive for its log files)

When a checkpoint occurs (various methods of determining when... search BOL for more info), the lazy writer (called lazy because it doesn't write until a checkpoint) reads the log entries since the last checkpoint and writes the changes to the database files (mdf file). Then the checkpoint is written to the log.

That's when simple recovery comes into play. If simple recovery is turned on, the log is truncated after each checkpoint. If full recovery is set, the log isn't truncated until it is backed up with truncate option. Full recovery with an appropriate backup/restore strategy enables point-in-time recovery.

Without a transaction log, SQL Server would be no better than Microsoft Word. When you're working in a Word document, what happens when your desktop machine is suddenly unplugged? Hopefully, Auto Save has some older version of your document for you. What did Auto Save get, though? Is it consistent? Were you in the middle of typing a sentence? Who cares, it's a Word document, it doesn't have data integrity rules! Now, if you unplug your SQL Server while it's running (should not be possible on a production machine, but just supposing), what happens? The transaction log is already written to disk. When SQL Server restarts, it recovers itself by reading the log. It rolls forward and back until it ends at a point where every transaction committed before it lost power is there, and consistent, in the database. This works even for Simple Recovery mode. It has to work, otherwise pulling the plug could corrupt your database.

The author of the article that started this disucssion has a good point. DBAs must understand how and why the transaction log works. It's fundamental to knowing what is possible with SQL Server. Knowing how recovery works gives you ammunition to convince a customer that an Excel workbook is not a database! (I haven't had to do that since the '90s... showing my age here...)

Post #527511
Posted Wednesday, July 2, 2008 1:38 PM


SSC Eights!

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

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 821, Visits: 2,028
Thanks Stephanie. They say that you learn something new everyday.

ATB

Charles Kincaid

Post #527591
Posted Wednesday, July 2, 2008 1:55 PM


SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:00 AM
Points: 473, Visits: 8,737
Thank you for pointing out the write-ahead specification (to both Charles and Stephanie). This is actually a standard that is followed to ensure that any changes to data are ACID compliant. I avoided this topic in the first article as for the article was target towards those with very little (if any) understanding on how the Transaction Log works. I will be covering the Write-Ahead aspect (and possibly brush upon ACID compliance), as well as the recovery models, in a future article.

Thanks,
James Rea


~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
Post #527603
Posted Wednesday, July 2, 2008 3:13 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: 2 days ago @ 12:02 PM
Points: 438, Visits: 402
I've been looking into the origins of the transaction log. This is a link to the site of the creator of the ARIES algorithm that seems to define the fundamental operation of the transaction log. MS SQL Server is listed among the products that implement this algorithm.

http://www.almaden.ibm.com/u/mohan/ARIES_Impact.html
Post #527654
Posted Wednesday, July 2, 2008 3:49 PM
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
Production versions of the t-log concept as applied to RDBMS pre-date the ARIES work by at least 10 years.

INGRESS,DB2 and PROGRESS had it in the late '70 and early 80's. Indicating R&D was well under way in the mid-70's.

Its implied by Codd's Rule #5 (1970): Comprehensive Data Sublanguage Rule
The database must support at least one clearly defined language that includes functionality for data definition, data manipulation, data integrity, and database transaction control.



Post #527666
Posted Thursday, July 3, 2008 3:29 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:15 AM
Points: 5,471, Visits: 1,402
Nice article.....


Post #527876
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse