Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2008
    Posts
    8

    Unanswered: trunc. log on chkpnt - Stop Transaction file gets truncated

    Hi All The link http://support.microsoft.com/kb/62866/

    suggests that you can set the transaction log never to truncate by setting "trunc. log on chkpnt" option to false. It also explains why it does not get truncated even if it is set to true.

    But I tried by selecting option FALSE as well as TRUE but still Transaction file gets truncated.

    EXEC sp_dboption 'DB_Name','trunc. log on chkpt', 'TRUE'

    EXEC sp_dboption 'DB_Name','trunc. log on chkpt', 'FALSE'

    Any idea??


    Thanks,

    Baddy

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by bol
    Starting with SQL Server 2000, setting the trunc. log on chkpt. option to true sets the recovery model of the database to SIMPLE. Setting the option to false sets the recovery model to FULL.
    How do you know that the log got truncated when you set the option to FALSE?
    Last edited by Pat Phelan; 06-02-08 at 15:51. Reason: Corrected a typo in the VBulletin directive
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2008
    Posts
    8
    DBCC log('DB_Name',4) command returns 1200 rows, after that i insert 100 records in the Table & now the DBCC log('DB_Name',4) command returns 200 rows.
    this means that Log file gets truncated some where thats why now its showing 100 records previously it was showing 1200 records.
    any idea?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmmm...got me there...I don't have 2K here, but in 2K8 I can't replicate what you're seeing...
    "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
    Jun 2008
    Posts
    8
    same is the case with SQL 2005

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Either you're having something over there that you did not fully describe, or I am overlooking something over here, that is so obvious, that I might be totally blind...(NO PUN or hint INTENDED!!!) Save the resultset before setting it to true and after, and then run something like CSDiff. See what it shows...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2008
    Posts
    8
    do have any idea how we set the option so that Transaction log file never truncated ?

  8. #8
    Join Date
    Jun 2008
    Posts
    8
    ur MSN 0r Yahoo Chat ID?

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jun 2008
    Posts
    8
    hotmail or yahooid?
    or you online now on gmail?

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    on all of them, I've already accepted your invite.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd love to know how this one turns out!

    -PatP

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It turned into an hour interrogation of what the guy wants to do with the log records after he reads them and converts them from hex to string. At the end, - he was just curious. But I could not replicate the log truncation the way he described it. Maybe I missed something, maybe he wasn't telling the whole thing...not sure.
    "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
  •