July 2, 2008 at 1:38 pm
Thanks Stephanie. They say that you learn something new everyday.
ATBCharles Kincaid
July 2, 2008 at 1:55 pm
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/
July 2, 2008 at 3:13 pm
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.
July 2, 2008 at 3:49 pm
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.
July 3, 2008 at 3:29 am
Nice article.....
July 8, 2008 at 3:34 pm
I'm still not clear why SQL Server doesn't auto shrink the transaction log when a full backup is done in the full recovery model. No transaction logs are needed once a full backup is done.
July 8, 2008 at 3:56 pm
john.leo (7/8/2008)
I'm still not clear why SQL Server doesn't auto shrink the transaction log when a full backup is done in the full recovery model. No transaction logs are needed once a full backup is done.
Let me take a stab at that for you. SQL server works on pages of data. When you do the full backup the server marks entries in the log as no longer needed. This may mark whole pages as not needed. The pages are kept in the file and get reused rather than growing the file.
It's an early form of recycling just like we used to do with those old glass pop bottles.
ATBCharles Kincaid
July 8, 2008 at 4:02 pm
Thanks Charles. I guess it makes some sense, but having a lot of unused pages sitting around for future use seems a bad use of space. We do a lot of BCP'ng to create a stage environment which I think fills the transaction logs. We have to do regular shrinkfiles once a week to reduce the bloated size of the t-logs since we start running out of disk space. It would make more sense to me to shrink the t-log automatically for the unused pages following a full recovery and then grow it again as necessary. BTW - thanks for pointing out the Sybase pre-dated MS for the transaction log (as did DB2).
July 8, 2008 at 4:05 pm
john.leo (7/8/2008)
I'm still not clear why SQL Server doesn't auto shrink the transaction log when a full backup is done in the full recovery model. No transaction logs are needed once a full backup is done.
Also, when using the Full Recovery Model, a full backup only backs up enough of the transaction log to ensure a consistant backup when it is restored.
😎
July 8, 2008 at 4:05 pm
Growing the t-log is an expensive operation.
The assumption behind not physically truncating it is that it got this big once before therefore it will get this big again.
If one really knew their dB transactions AND sql server had a PHYSICAL truncate on checkpoint option then the t-log could be automatically pruned to the minimum size w/o adding extents. But alas....
July 8, 2008 at 4:34 pm
To illustrate further on what Charles is saying, think in the terms of a large organization that has many, many transactions per day. Imagine that your servers are at peak use in terms of CPU, RAM, and I/O utilization. The principal idea is that if you run normal backups, and have normal database activities over the periods of time between backups, then the transaction log will grow only to the size needed to store the transactions until the next backup…and this would result in the least amount of wasted space, as well as the least amount of resources being used.
If the transaction log were to be shrunk after a full backup, then that would take up resources that you might not want to be used. Let’s say that it were to be shrunk, so now you have a transaction log that is empty and is nearly nothing in size because you shrunk it as far as possible. Now, assuming you have equivalent activity this month (or whatever time frame is between the next backup) as you did last period of time, you know the transaction log will end up the same size at the end. At some point, which you probably wouldn’t control (unless you are very proactive at monitoring and adjusting file sizes as needed) the transaction log would then have to increase in size as it fills up and runs out of room; thus again using up valuable resources.
In most cases, this is wasteful in resources on the server. Most, but not all, businesses have some sort of regular amount of data transactions occurring. You could in fact even disable the auto-growth altogether. Then in this case the log would never grow, nor shrink in size. However, if you try to write to the database after the transaction log is filled, you will receive an error and the transaction will be rolled back.
With all that said, the primary purpose of the transaction log staying a certain size once it’s reached that size (by default settings) is purely for performance, which is what most Enterprise companies are looking at; but, also helps most small/medium users at the same time. There is not a specific guideline (that I’m aware of) that requires this behavior, I think it’s a ‘more people are helped than not’ thing.
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
July 8, 2008 at 4:43 pm
John,
Have you considered using the Bulk-Logged Recovery Model?
BOL has a good section called "Prerequisites for Minimal Logging in Bulk Import" (http://msdn.microsoft.com/en-us/library/ms190422.aspx)
Maybe, this will help ease your burden with the transaction log!
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
July 9, 2008 at 8:20 am
It's really great to have such an active (and knowledgeable!) community. We could use bulklogged recovery model and that would mitigate the growth issue, but the question was more theoretical. I could never find on the MS sites the principal behind not auto-shrinking but the community has answered it for me. Thanks.
July 9, 2008 at 11:38 am
You are welcome, John. I might also advocate adding another drive to the server and moving the log file there.
(1) It will get that pesky log file out of the way of your other files.
(2) Being on a separate spindle from your .mdf file you will see some performance improvement.
(3) The log file being the only thing on that drive you can keep an eye on the size with just the explorer.
Oh, while you are under the hood installing the new drive can you cram any more fast memory into the box?
ATBCharles Kincaid
July 17, 2008 at 9:11 am
Nobody has yet mentioned another reason for not shrinking the log on truncate: physical file fragmentation. If, when the log grows, it can't find a contiguous area on the drive, it will go where it can fit. Lack of contiguity means the disk may have to spin more times to statisfy a read request. This condition is known as fragmentation. If you set your transaction log to the size it needs to be and then leave it (assuming you do this while there is still plenty of space on the disk) then you won't have this problem.
Of course, if you're lucky enough to have a separate dedicated physical disk for every log file, then fragmentation isn't going to be a worry for you.
John
Viewing 15 posts - 16 through 30 (of 64 total)
You must be logged in to reply to this topic. Login to reply