Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106

    Unanswered: Unable to truncate everything from tempdb online log

    Hey guys,

    I'm having difficulty completely truncating my tempdb online log. It sits at 25%, and remains allocated even after I've truncated the log with backup log tempdb with truncate_only. Now, I would assume this to be normal if there is a spid who hasn't commited or rolled back a transaction, or if checkpoint hasn't run. However, running DBCC opentran('tempdb') shows me that there are no open transactions at that time, and checkpoint appears to be running fine.

    Can anyone give me any hints on which way I should point with my investigation.

    Regards,
    -Kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and WHY are you doing this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    The transaction log for tempdb had previously filled up for an unknown reason, and I'm trying to figure out why..

    thanks,
    -Kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Kilka101
    The transaction log for tempdb had previously filled up for an unknown reason, and I'm trying to figure out why..

    thanks,
    -Kilka

    tempdb log file is not like other log files for other databases. You're not trying to preserve transactions here in order to recover the database. If it filled up, that means (I believe) that it grew and filled up the disk on which it resides. Often it's a symptom of something else going on (like another database growing out of control or another logfile growing too big).

    Like I said, it's a symptom; you'll want to look for your cure in another location.

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or a bad cross-join....

    Regardless, you shouldn't ever need to truncate tempdb.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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