Post reply

Auto shrink Tempdb

  • January 21, 2004 at 9:28 am

    #108842

    I currently have tempdb set to auto shrink.  I am wondering if this is the best setting, as whenever a large stored procedure or query is running it has to grow the database, and then shrink.  Tempdb is shrunk once a week with database maintainence.

  • January 21, 2004 at 1:22 pm

    #491027

    Tempdb cannot be set to auto shrink (at least not in SQL Server 2000), so that's definitely not the best setting. 

    All databases should be initially sized to the maximum space you calculate they will need, including tempdb, e.g.:

    ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 100)

    ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = 10)

    This will improve performance by minimizing autogrowth and file fragmentation.  If tempdb does grow beyond the configured size, it will be reset when the sqlserver service is stopped and restarted.



    --Jonathan

  • January 22, 2004 at 12:33 pm

    #491194

    U can have a job to shrink tempdb...

    DBCC SHRINKDATABASE ([tempdb])

    and schedule it to run twice a day or as required.

  • January 23, 2004 at 8:10 am

    #491376

    Hi, This is probably a really stupid question, but why do you need to shrink the tempdb? If it was necessary for it to grow to a certain size, won't it happen again? I can't see how it would be a benefit to shrink the tempdb. Can someone explain?

  • January 23, 2004 at 8:15 am

    #491383

    That's an excellent question.



    --Jonathan

  • September 16, 2004 at 2:13 am

    #523276

    If you're low on disk space and can't cram any more disks into your server you might need to reclaim the empty space from the Tempdb until you can sort the problem out.

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

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