Results 1 to 6 of 6

Thread: Transaction Log

  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Unanswered: Transaction Log

    hi,



    begin trans

    --some INSERT operation on my Tables

    commit trans


    what happens to the transaction log , when the above operation is performed

    will the entire statements between begin trans & commit trans be logged in transaction log ?

    what will be the contents of the transaction log when the sql server is started.
    will it be an empty file?

    after issuing a CHECKPOINT , will the transaction log be cleared?
    Cheers....

    baburajv

  2. #2
    Join Date
    Sep 2005
    Posts
    6
    Did you find out where is the transaction log is located , I' looking for it too. Please let me know. Thanks a lot. Kiana


    Quote Originally Posted by baburajv
    hi,



    begin trans

    --some INSERT operation on my Tables

    commit trans


    what happens to the transaction log , when the above operation is performed

    will the entire statements between begin trans & commit trans be logged in transaction log ?

    what will be the contents of the transaction log when the sql server is started.
    will it be an empty file?

    after issuing a CHECKPOINT , will the transaction log be cleared?

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    when a transaction is written to the log file, the operations within the transaction are added to the log and when the commit is issued, the transaction is written to the datafile upon the next checkpoint.

    when SQL server is started (and during certain types of Restore operations), recovery is performed. the uncommited transactions within the log are rolled back and the committed transactions that have not been written to the datafile, are then written to the datafile. then the database is returned to a "usable" state.

    checkpoints do not cause the log to be "cleared out", the truncating of the TLog comes from an explicit backup log statement by adding the with truncate_only clause.

    checkpoints are incremental log marks that (among other things) signal the point in which the revcovery interval is staged.
    remember recovery from before? the amount of time recovery takes is tied to the length of time between your checkpoints with a system variable called recovery interval. if you set the recovery interval to a value of 3, then checkpoints will occur far enough apart so that when the transactions collected in the log (between the checkpoints) need to be recovered, it would take about 3 minutes.
    so if transactions increase in volume, the checkpoints happen more often and as transactional activity decreases, so does the interval between the checkpoints.

    check out BOL.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm...Almost right, but not quite...Here we don't know what the recovery mode the user database is in, but in the middle of your post you assume that it's full.

    To answer the original question, no data is written to the data device without being written to the tlog first. So yes, the entire contents of BEGIN TRAN...COMMIT are written to the tlog IMMEDIATELY after the COMMIT is encountered by QP.

    As far as the contents of tlog when the service is restarted...this depends on when the service was stopped in respect to the time when COMMIT was issued. The reason for this to be a vague answer is because data is written to the data device asynchronously, and the signal to write is triggered by CHECKPOINT nature of which Scott somewhat explained.

    But the trick here is in the following, - EVERYTHING that is found in the tlog at the time of database recovery is REPLAYED, regardless of whether the affected data can or cannot be found in the data devices. This is how SQL Server guarantees the integrity (not usability) of the database when it is fully recovered.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    "somewhat explained" ??!!??

    I'll say one thing for you, you are consistent and you definitely dont play favorites.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, I am all about details, you know that

    EDITED: And what are you doing up so late? Even here it's 10PM!!!
    "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
  •