Post reply

how to reduce tempdb size in production in ms sql

  • July 20, 2009 at 8:30 am

    #133100

    Hi

    how to reduce tempdb size in production in ms sql 200/2005 with out any fail in production (i.e without restart ,without affecting the production without any loose )

    😎

    Thanks
    Parthi

  • July 20, 2009 at 8:37 am

    #1026987

    Look out for DBCC SHRINKDATABASE OR DBCC SHRINKFILE in BOL.

    In most of the cases, restart is the only option.....


    Pradeep Singh

  • July 20, 2009 at 8:41 am

    #1026995

    The server may slow down with SHRINKFILE, so be aware of that. You might be better off resizing the files, or if they were small and grew, just restarting the server. SQL starts quickly, and it is often back up by the time your phone rings

  • July 20, 2009 at 8:56 am

    #1027018

    Hi!

    Why do you need to reduce the size of tempdb?

    I mean, what exactly is happening and why is the size of tempdb a problem?

    I don't know of any good way to actually reduce the size of tempdb without restarting SQL Server (shrink will tend to not work well on tempdb), but my experience is that if tempdb grows out of of control, it's usually caused by a poorly written application or procedure which does not clean up after itself. Tempdb is intended as a temporary work database only, so anything stored there should be deleted immediately once it's no longer needed. When this does not happen, tempdb can grow very fast and the only real solution is to find the offending applications / procedures / users and change their behavior.

    You can delete stuff from tempdb just like you can delete stuff from any other database, and while this will not actually reduce the size of tempdb, it will release space which can then be reused. However, don't try this at home unless you know what to delete - and finding out what to delete can often take some time, not to mention actually deleting it without harming any innocent bystanders in the process. Hence, to the best of my knowledge, the only "quick and easy" workaround is to restart SQL Server every time tempdb hits the ceiling or to keep throwing more disk at the problem until you have a permanent solution.

    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)

  • July 20, 2009 at 9:21 am

    #1027060

    Unless there were some "run away" query that blew TempDB to an insane size, don't shrink TempDB... it's just gonna grow again.

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

  • July 20, 2009 at 1:59 pm

    #1027276

    Before you shrink tempDB, read through this: http://support.microsoft.com/kb/307487

    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

  • June 19, 2012 at 12:57 am

    #1502597

    Hi

    I think no need to restart server,restaring sql service

    may solve the problem.

    Ali
    MCTS SQL Server2k8

Viewing 7 posts - 1 through 6 (of 6 total)

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