Shrinking TEMPDB files

  • I know this topic has been covered, But i have a 284 GB tempdev file and it will not shrink. What is the easiest way to shrink it? Will bouncing SQL server resolve this? If so then wanted bouncing to be last resort. Went through SSMS to no avail and script out to no avail...Am I missing something??

  • Yes, bouncing the service will resolve it. If scripting the shrink did not resolve it, it may depend on the script. But you are possibly down to the last resort though.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Kasnut (6/26/2012)


    I know this topic has been covered, But i have a 284 GB tempdev file and it will not shrink. What is the easiest way to shrink it? Will bouncing SQL server resolve this? If so then wanted bouncing to be last resort. Went through SSMS to no avail and script out to no avail...Am I missing something??

    The only safe way to shrink temdb is to restart SQL Server. The problem is, since your tempdb database grew to 284GB in size, it probably will again. If this is a problem (running out of disk space on the disk drive where tempdb resides for example), then you need to start looking at the processing on your system to try and determine what is causing tempdb to grow so much. Remember, tempdb is used by all of your databases on the instance.

  • Lynn Pettis (6/26/2012)

    The only safe way to shrink temdb is to restart SQL Server.

    i too agree to it.

    Regards
    Durai Nagarajan

  • Reboot is a pathetic solution answer for a routine situation that is nearly 100% resolvable without a reboot. I'd rather kill random spids until the shrinkfile command magically works than Reboot. Just suggesting a reboot is the "SAFE" way should make you give up your dba card. The safe way to getting fired maybe... what kind of applications are you supporting obviously not 24/7 Online type software packages if you consider reboot a "safe" anything...

    Find out what is using tempdb, has open transaction, doing massive writes, spooling to tempdb whatever and kill it. Shrink the dang file and cap it if it causes you issues growing uncontrolled, put in place monitoring to determine what cause the bloating if you don't know...

  • thadeushuck (6/28/2012)


    Reboot is a pathetic solution answer for a routine situation that is nearly 100% resolvable without a reboot. I'd rather kill random spids until the shrinkfile command magically works than Reboot. Just suggesting a reboot is the "SAFE" way should make you give up your dba card. The safe way to getting fired maybe... what kind of applications are you supporting obviously not 24/7 Online type software packages if you consider reboot a "safe" anything...

    Find out what is using tempdb, has open transaction, doing massive writes, spooling to tempdb whatever and kill it. Shrink the dang file and cap it if it causes you issues growing uncontrolled, put in place monitoring to determine what cause the bloating if you don't know...

    Obviously you would not reboot until approval to do so. Who would wantonly reboot a server on a whim? Further more who would kill random spids. If killing a random spid is safe, it sounds like your data is not very important and a reboot would be no worse.

    Flipside - why even shrink the tempdb database? Sure you may need to shrink it if out of disk space. But, if your drive is full then your 24/7 application is already halted and you are in an outage.

    I agree on the monitoring of tempdb to discover usage.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • thadeushuck (6/28/2012)


    Reboot is a pathetic solution answer for a routine situation that is nearly 100% resolvable without a reboot. I'd rather kill random spids until the shrinkfile command magically works than Reboot. Just suggesting a reboot is the "SAFE" way should make you give up your dba card. The safe way to getting fired maybe... what kind of applications are you supporting obviously not 24/7 Online type software packages if you consider reboot a "safe" anything...

    Find out what is using tempdb, has open transaction, doing massive writes, spooling to tempdb whatever and kill it. Shrink the dang file and cap it if it causes you issues growing uncontrolled, put in place monitoring to determine what cause the bloating if you don't know...

    And shrinking tempdb can cause corruption that could take down your instance as well.

    I am not recommending a restart of SQL Server to solve a problem, I only stated that restarting SQL Server is the only safe way to shrink tempdb.

    In fact, I specifically stated that tempdb could easily grow back to the same size and that what was needed was to identify what was causing tempdb to grow and determine if those processes could be modified/changed to reduce the impact on tempdb.

  • thadeushuck (6/28/2012)


    I'd rather kill random spids ...

    Be careful, now... You could end up with SPIDs stuck in a rollback using 1 full CPU each that never resolve until you do a reboot.

    --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)

  • Lynn Pettis (6/26/2012)


    Kasnut (6/26/2012)


    I know this topic has been covered, But i have a 284 GB tempdev file and it will not shrink. What is the easiest way to shrink it? Will bouncing SQL server resolve this? If so then wanted bouncing to be last resort. Went through SSMS to no avail and script out to no avail...Am I missing something??

    The only safe way to shrink temdb is to restart SQL Server. The problem is, since your tempdb database grew to 284GB in size, it probably will again. If this is a problem (running out of disk space on the disk drive where tempdb resides for example), then you need to start looking at the processing on your system to try and determine what is causing tempdb to grow so much. Remember, tempdb is used by all of your databases on the instance.

    +1

    --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)

Viewing 9 posts - 1 through 8 (of 8 total)

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