Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002

    Unanswered: Tempdb size shrinking when it happend ?

    On what scinarios the size of the Tempdb will come to the normal stage.

    The situation is I have run a sqlscript in QA after running the same I have observed that the size have increased to 500 MB .
    Even though the squery have completed successfully since a long time the size of the Tempdb is not comming to the normal size.

    I would like to know Generally on what condition the size of the Tempdb will come to normal state.Like after a duration of how long this comes to the normal state.
    Since restarting the server is not possible i am seeking for some alternative solution.
    Is sql server automatically takes care of this .. ie to shrink the database ?

  2. #2
    Join Date
    Dec 2002

    Re: Tempdb size shrinking when it happend ?

    I don't know the exact parameters for when SQL will shrink TempDB (how often, to what size, etc). I DO know that you MUST set a max size limit or you will get into big trouble when it grows out of control and soaks up all your HD space.

    It's size will vary depending on loads and other things. 500MB doesn't seem too bad to me. 'Course I don't know anything about the rest of your dbs.



  3. #3
    Join Date
    Sep 2003
    Dallas, Texas

    Re: Tempdb size shrinking when it happend ?

    The only way to shrink tempdb size, the safest way I mean, is to stop and restart SQL. If that's not a option, then just leave it alone. Your process won't have to increase the size of tempdb when they run, so it will save you some overhead. The rule that I follow in the last few years and it has worked quite well is to set the tempdb as big as the RAM. Of course, everyone has different situations and different setups they need to take care of.

    Hope this helps.

  4. #4
    Join Date
    Feb 2002
    Other than restarting the service, you can use dbcc shrinkfile/shrinkdatabase/alter database modify file ... Is 500 mb an issue for your server ?

  5. #5
    Join Date
    Oct 2003


    As a general rule of thumb, database products won't "shrink" their temporary files. Changing the size of a file is an expensive operation. Programs will usually increase the size of the file when they must (often growing the file by a large chunk, more than they immediately need), and then recycle the space using their own internal logic. The file never actually shrinks.

    Also as a general rule of thumb, don't worry about it. If available disk-clusters exist for the storage of data, it really doesn't matter if they're inside an existing file or not. If you find yourself running out of storage capacity, buy more drives. Seriously.
    ChimneySweep(R): fast, automatic
    table repair at a click of the

  6. #6
    Join Date
    Jul 2003
    San Antonio, TX
    Autogrowth allows the server to acquire additional space for the specified file based on increment unit of measurement (percent or MB.) If the file grew, then stopping and starting the service is not going to release unused allocated space. If you look at the file size after bouncing the service it'll be of the same size as before. For user databases you can set Autoshrink setting on and it'll take care of releasing the allocated space back to OS if it's not used. In the case of tempdb this option is not available, so you can use Shrink database option or as mealejr suggested, - dbcc shrinkfile/shrinkdatabase/alter database modify file command line utilities.

Posting Permissions

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