Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115

    Unanswered: Tempdb Full Pls help!!!

    Hi all,
    Greetings,

    Sqlserver 7,

    I am geeting the below error in application log
    Error: 9002, Severity: 17, State: 2
    The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.

    I went and saw there is enough space in the drive were Tempdb resides.
    I cant restart the Sqlsever as it is production Machine.


    Pls help give suggestion to over come this

    Waiting for reply
    TIA
    Adil

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use BACKUP LOG. I'd suggest using:
    Code:
    BACKUP LOG tempdb WITH TRUNCATE_ONLY
    as a first step.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to identify what process attempts to fill it up and tell the guy to stop doing it in production. The statement suggested above may not work if there are open transactions. Do "dbcc opentran('tempdb')" and act upon the SPID that is reported in the output. Do not issue KILL statement unless there is no other way to get rid of the open transaction. See if the offender can issue COMMIT so that the log can be flushed.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I agree, I was just looking to apply triage to help the dba get to the point where they could hunt down the offender. We're on the same track, and they'll want to follow your advice for the long term. I just hope that my suggestion buys them some breathing room to do that.

    -PatP

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578

Posting Permissions

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