Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006

    Unanswered: Commiting transactions/Truncating transactional logs/Replication Latency


    I have multiple questions and would appreciate any suggestions in resolving them. I'm a novice to these issues.

    1) First of all, what is the exact command to setup the trunc. log on chkpt. option on for a transactional log of a SQL Server 2000 database? Is this option on by default? I have noticed for one of the databases I'm managing that the transaction log was over 12 GB, while the db was only 425 MB.

    2) How's it possible to run a DBCC TRACEON to see the content of the transaction log to see if we are having any issues with any uncommitted transactions, i.e. updates, inserts, and/or deletes.

    3) What are the commands to truncate inactive transactions and increasing the readbatchsize?

    4) lastly, how do I validate transactional replication via checksum and find valid latency between a small number of changes that need to be committed between publisher and subscriber.

    Thank you so much in advance!

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    Regarding your log issues, what is your backup strategy?
    Are you backing up the database regularly, and also backing up the logs regularly? If the logs are being dumped on a regular schedule, then they should not be growing so large.
    If you set your logs to truncate on checkpoint, you will lose the ability to recover data between backups.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Jun 2004
    Long Island
    Truncate Log on Chkpt = recovery mode to "simple", under options tab.

  4. #4
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    this looks like home work, so lets ask him a couple of questions, oh wait...
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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