Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004
    Posts
    16

    Unanswered: Multiple Transaction Logs

    I have just come from an Oracle background and am trying to equate SQL server methodologies to Oracle. I have a couple of questions based on the same principle which is the usage of multiple transaction logs. This is what I have been able to find out from the docs and other posts:

    If you have multiple transaction logs they will all be used in a sequential manner before being wrapped around. That is, they will all be filled before they are reused.

    My first question is will they be used sequentially or concurrently. SQL Server seems to stripe everything so I am inclined to believe the latter. This question has been asked before but from other posts/docs there seems to be a difference of opinion. I am of course equating transaction logs to redo/archived logs so I want to know categorically (links would be greatly appreciated) as it will have a huge impact on any disaster and recovery plans that I implement.

    The second question is a spin off and may or may not be relevant dependant on the answer of the first. If the transaction logs are written to concurrently, that is, entries are striped, why bother with multiple logs? I don't see any benefits. In fact, in a recovery scenario recovering transactions from mutliple striped logs would appear to adversely affect MTTR.

    Feel free to point out the errors of my ways as I am all ears and eager to learn.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My advice is to get the poop straight from the horse.

    I'd also recommend two books, the first is the SQL 2000 Resource Kit which has a great chapter on Oracle to MS-SQL, along with a lot of other VERY useful information. The second is Inside Microsoft SQL Server 2000.

    Peruse a bit, then come back... I'm sure that you'll still have questions, you'll just be able to form them a bit better!

    -PatP

  3. #3
    Join Date
    Oct 2004
    Posts
    16
    OK, found the answer, I think. The logs are used sequentially. In case anybody else wants the answer, when there are multiple log files, the first one fills up, then the logical log moves on to the second physical logfile etc. When the last physical log is filled up it then wraps around to the beginning of the first physical log again. This is the best description that I have found and it implies, but does not say categorically confirm that if you have declared two logfiles in the create database statement, i.e.

    log on
    (name='test1_log1',
    filename='E:\SQLServer\test1\Logs\test1_log1.LDF',
    size=5mb,
    maxsize=10mb),
    (name='test1_log2',
    filename='E:\SQLServer\test1\Logs\test1_log2.LDF',
    size=5mb,
    maxsize=10mb)

    test1_log1 would be used, then test1_log2 before wrapping around back to test1_log1.

    I had actually researched this issue quite considerably before I posted this question, including reading 3 SQLServer books. The purpose and use of the log is described, but not this issue. If you look at the following post you will can see that there is a difference of opinion where some think they are used concurrently and some think that they are used sequentially:

    http://www.mcse.ms/showthread.php?th...0&pagenumber=1

    I tried to imply this before when I posted the thread. My main problem was that folks couldn't agree between themselves how they were used so I was hoping for some clarification.

    Thans for responding anyway.
    Last edited by sramshaw; 11-01-04 at 14:24.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No problemo!

    I've never seen an instance where more than one log file was ever in use at one time. Theoretically there is a tiny window when two adjacent (sequential) log files might be active for the duration of one DML statement, but I've never seen that happen in the real world.

    I've found lots of discussions of log files, and how people think those files are used. I've never seen anything to contradict any of the sources that I sited in my previous posting. I don't believe that there is any way to have more than one log file active for a given database at one time, beyond the tiny window theoretically allowed by the log sequencing mechanism.

    -PatP

  5. #5
    Join Date
    Oct 2004
    Posts
    16
    That was the reason why I was looking into it. All of the configurations we have here have one log, but the docs say you can specify more than one. In Oracle more than one redo log group is mandatory and the reason is quite straight forward. In SQL Server the transaction log can expand, so one of the main reasons for having more than one is eliminated. I was wondering why you would want more than one, other than the lack of disk space to allow it to expand or manual duplexing (which obviously not the case as I have discovered). I was thinking that it may provide more flexibility in a recovery scenario or maybe performance reasons when it comes to truncating and backing up the transaction log(s). I have see some posts where this action can sometimes adversely effect user performance.

    Maybe I am digging a bit too deep too early in the process, and I'm sure everything will become clear with the passage of time, but any pointers on this subject would still be appreciated.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are probably a gazillion different reasons for allowing multiple log files, but the one that I've hit most frequently is when someone sets up a database (and of course its log file too) on a drive that is "bigger than they'll ever need". Through experience, I've discovered that only infinity is really infinity... For one reason or another, you eventually use "more than you'll ever need" in almost every case! When the database consumes all of the available disk, you can create a new log file on a different disk drive in order to get enough breathing room to fix the underlying problem.

    I'm sure that there are lots of other reasons, but that one alone is enough to justify the functionality to me.

    There is a lot of information within BOL (SQL Server Books Online), but the real meaty stuff is in the books that I cited in my previous posting. Kalen has forgotten more about indexing than I'll ever know, and the Resource Kit ought to be required reading for someone with your technical background... It gets into the "care and feeding" that an Oracle DBA is accustomed to, explains which things are important to SQL Server and which are irrelevant.

    If you are in a major metro area, it doesn't hurt to look for a user group. Even if you aren't in a major metro, check out PASS for more insight.

    -PatP

  7. #7
    Join Date
    Oct 2004
    Posts
    16
    That's the conclusion I was coming to. I appreciate the input. I'm coming up to speed quite nicely on the fundamentals of SQLServer but still receptive to any direction that might facilitate this process more easily. Thanks.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sure,

    SQL Server is simply easier than Oracle....

    Notice I didn't say better...

    The multiple Redo Logs allow for some pretty substantial amount of transactions...

    In all cases I've ever seen in SQL Server, ther's only 1..

    I dump tranny logs every 10 minutes in some environments....so in that cas etheres more than 1...I guess that's where the confusion comes in...

    Redo Logs are like partitioned logs...which sql server doesn't have...I don't think (And you know, that happens waaaay to much)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    From Books Online:

    Transaction Logs
    A database in Microsoft® SQL Server™ 2000 has at least one data file and one transaction log file. Data and transaction log information is never mixed on the same file, and individual files are used by only one database.

    SQL Server uses the transaction log of each database to recover transactions. The transaction log is a serial record of all modifications that have occurred in the database as well as the transaction that performed each modification. The transaction log records the start of each transaction. It records the changes to the data and enough information to undo the modifications (if necessary later) made during each transaction. For some large operations, such as CREATE INDEX, the transaction log instead records the fact that the operation took place. The log grows continuously as logged operations occur in the database.

    The transaction log records the allocation and deallocation of pages and the commit or rollback of each transaction. This allows SQL Server either to apply (roll forward) or back out (roll back) each transaction in the following ways:

    A transaction is rolled forward when you apply a transaction log. SQL Server copies the after image of every modification to the database or reruns statements such as CREATE INDEX. These actions are applied in the same sequence in which they originally occurred. At the end of this process, the database is in the same state it was in at the time the transaction log was backed up.


    A transaction is rolled back when you back out an incomplete transaction. SQL Server copies the before images of all modifications to the database since the BEGIN TRANSACTION. If it encounters transaction log records indicating that a CREATE INDEX was performed, it performs operations that logically reverse the statement. These before images and CREATE INDEX reversals are applied in the reverse of their original sequence.
    At a checkpoint, SQL Server ensures that all transaction log records and database pages modified are written to disk. During the recovery process of each database that occurs when SQL Server is restarted, a transaction must be rolled forward only when it is not known whether all the data modifications in the transaction were actually written from the SQL Server buffer cache to disk. Because a checkpoint forces all modified pages to disk, it represents the point at which the startup recovery must start rolling forward transactions. Because all pages modified before the checkpoint are guaranteed to be on disk, there is no need to roll forward anything done before the checkpoint.

    Transaction log backups enable you to recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure. Transaction log backups should be a consideration in your media recovery strategy. For more information,
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Data devices are written to in concurrent fashion (if more than 1 is defined), while log devices are ... well, that was already mentioned. There is a very good reason to have multiple small sized trx log files defined for servers with 5+ user databases and 2+ disk arrays dedicated to log devices.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey Brett, what's up with citing BOL article that has nothing to do with the question?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •