Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    London
    Posts
    2

    Unanswered: dump transaction log on tempdb takes 30+ mins

    We're experiencing an intermittent problem whereby the logsegment in tempdb gets <40% space free and a threshold procedure is fired off. The proc collects some information about what was running at the time then dumps the transaction log with truncate_only. This regularly takes
    around 30 mins to complete.

    While the dump is in progress, the log continues to grow and further thresholds are passed.

    We're unable to figure out why it takes this long, as we're just truncating the log not saving it to an O/S file. Any ideas would be much appreciated.

    Thanks & regards

    Graham

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Put the option "trunc log on checkpoint" to "ON" for tempdb

  3. #3
    Join Date
    Feb 2004
    Location
    London
    Posts
    2
    The "trunc log on chkpt" option is already on. In fact, it is *always* on for tempdb, regardless of what you do using sp_dboption (I think it may actually be impossible to turn off this option on tempdb).

    The problem is that just relying on the checkpoint handler to truncate the log is not sufficient. It is not quick/frequent enough to keep tempdb under control. We have a big spike of tempdb activity around 10pm, and we need to act fast to keep the log under control, hence we use thresholds.

  4. #4
    Join Date
    Jan 2003
    Posts
    62
    Yes, relying on the checkpoint handler to truncate the log is not sufficient and this is not to be blame. Keep in mind how the process works. Once a minute, the checkpoint process wakes up and checks each database to see if it needs to be checkpointed. If the 'truncate log on chkpt' option is on, it will issue a checkpoint if there are more than ~50 log records in the log since the most recent checkpoint. If not, then it skips the database. It will only try to truncate the log if it issues a checkpoint. Also, it is possible for a long running transaction to be in progress, then the checkpoint process issues a checkpoint, but the log doesn't get truncated because the transaction was open!

    Originally posted by muntfish
    The "trunc log on chkpt" option is already on. In fact, it is *always* on for tempdb, regardless of what you do using sp_dboption (I think it may actually be impossible to turn off this option on tempdb).

    The problem is that just relying on the checkpoint handler to truncate the log is not sufficient. It is not quick/frequent enough to keep tempdb under control. We have a big spike of tempdb activity around 10pm, and we need to act fast to keep the log under control, hence we use thresholds.

  5. #5
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    1) You can change the checkpoint frequency confiuring "recovery interval in minute"
    2) You can add a threshold

Posting Permissions

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