SQL Clone
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 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 8754
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/
Stephanie Giovannini
Stephanie Giovannini
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1515 Visits: 1538
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.
Charles Kincaid
Charles Kincaid
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: 1631 Visits: 2384
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.

ATBCharles Kincaid
Rudyx - the Doctor
Rudyx - the Doctor
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4747 Visits: 2503
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."
Stephanie Giovannini
Stephanie Giovannini
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1515 Visits: 1538
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...)
Charles Kincaid
Charles Kincaid
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: 1631 Visits: 2384
Thanks Stephanie. They say that you learn something new everyday.

ATBCharles Kincaid
James_DBA
James_DBA
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 8754
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/
Stephanie Giovannini
Stephanie Giovannini
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1515 Visits: 1538
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
TechnoPeasant
TechnoPeasant
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

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



Anipaul
Anipaul
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7227 Visits: 1407
Nice article.....



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