Post reply

Shrink Tempdb Issue

  • February 24, 2004 at 11:53 am

    #83837

    We have a Tempdb that is in Simple Recovery Mode so the log file is not being used. The .mdf makes up the 11 GB of Tempdb. The .ldf is only 1 MB. The Tempdb shows 11549 MB allocated and 11547 MB free. I am wondering if the 11547 MB is actually free or if the space is being used by empty pages or zeroes. Do I need to perform Item Number 1 below to really find out or shrink the database (.mdf)? Would it take a long time to perform either 1 or 2? I am wondering if I should do this during the day. There is not a lot of activity on our database during the day.

    1) Shrink DB on Tempdb that is 11 GB in size with the 'Move pages to beginning of file before shrinking' option checked.

    2) Shrink File on the Tempdb .mdf file with the 'Compress pages and then truncate free space from the file' option checked.

    Thanks in Advance,

    Kevin

     

  • February 24, 2004 at 12:07 pm

    #495397

    Your TEMPDB has been growed and only very little is being used. You can just shrink the MDF file to reduce the spaces it allocated.

  • February 24, 2004 at 12:09 pm

    #495398

    It should also rebuild at its original size when the server is restarted.

    The other item is that your .ldf is used for transactions, but committed transactions are removed at a checkpoint. a long running transaction can still fill the .ldf file and force it to grow.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • February 25, 2004 at 10:31 am

    #495624

    Adding to this question, since I have the same issue.

    Can you use DBCC SHRINKDATABASE anytime to shrink the tempDB or should this only be done when the SQL server does not have a lot of activity.

    Also, how can you tell what the original size would be if you stopped and restarted SQL services without actually doing this. When I use Enterprise Manager and set the view to Taskpad, I notice a Date Created and Size - is this the original size set?

    Thanks for your help.

    Cam

     

Viewing 4 posts - 1 through 3 (of 3 total)

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