Handling Tempdb space full situtation?

  • How can we handle the situation if the tempdb space was full?

    reply with more than 2 diff answers?

  • you need to alter the tempdb database in this case(means need to increase the size of DB) from the query analysir, in this situation you might see all the DBs would disappear. But dont worry it would come back once temp db is normal.

  • The real problem is that you need to identify why it is full, first.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • rames.net (7/21/2008)


    reply with more than 2 diff answers?

    Why?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • TEMP DB FULL:

    first know the reason why it is full then shrink the

    temp db,or u can add one more temp file to that from

    MMS.:P

  • We also need to know what you think "full" is... how many bytes is your TempDB?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Is tempdb set to autogrow? If not, that might be the issue as well. One application I support makes liberal use of temp tables. A guy I knew at another company using the same app was complaining about tempdb filling up. It turned out the DBA's at that company restricted tempdb to 50MB, and refused to let it autogrow, giving some specious reason. It took months to get htem to autogrow tempdv, at which point, the issues all went away.

    Rebooting the servers on a regular schedule helps as well, as tempdb gets cleaned out during the startup process.

  • Heh... 50MB for a TempDB... we start our TempbDB up at 12 Gig!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The DBA's that specified the 50MB were of the opinion that aproperly designed application doesn't need more tempdb. I was glad I didn't have to deal with them. Our tempdb's typically run 3-6GB.

  • Sounds to me like an exam question or homework. Otherwise why ask for 2 different answers. I would suggest that the OP read their course material and find the answers that way... They will likely learn more that way.

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Jeff Moden (7/21/2008)


    The real problem is that you need to identify why it is full, first.

    As Jeff said, this is key. I've seen adhoc queries fill tempdb and bring the system to a halt. The genius that intalled SQL when that incident occurred, put tempdb on the c:\ drive and it ultimately filled the disk. When I assumed the role of DBA, things got moved to appropriate physical partitions with plenty of room for growth and setup appropriate monitoring of the system. There are plenty of processes that utilize tempdb, index rebuilds being but one. I don't think we can give solid advice unless we know what/how it filled up. Restarting the SQL service will recreate tempdb but that's a short term fix as I'm sure whatever filled it up once will execute again, putting you in the exact same situation.

    Edit - All my tempdb databases are minimum of 5GB

    -- You can't be late until you show up.

  • Ross McMicken (7/23/2008)


    The DBA's that specified the 50MB were of the opinion that aproperly designed application doesn't need more tempdb. I was glad I didn't have to deal with them. Our tempdb's typically run 3-6GB.

    On my main system, TempDB is 6 files of 8 GB each.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ross McMicken (7/23/2008)


    The DBA's that specified the 50MB were of the opinion that aproperly designed application doesn't need more tempdb. I was glad I didn't have to deal with them. Our tempdb's typically run 3-6GB.

    Wow... That's amazing, Ross... I haven't heard anything like that in a long time... please tell me those DBA's weren't actually certified...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • GilaMonster (7/23/2008)


    On my main system, TempDB is 6 files of 8 GB each.

    Now THAT sounds like a fun system to be on and that the DBA (you, Gail?) might actually have his/her ducks lined up.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (7/23/2008)


    Wow... That's amazing, Ross... I haven't heard anything like that in a long time... please tell me those DBA's weren't actually certified...

    Depends. Do you mean certified as in qualified or certified as in insane?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 57 total)

You must be logged in to reply to this topic. Login to reply