Post reply

Shrinking Tempdb Revisted

  • February 17, 2005 at 11:00 am

    #86906

    Hello all ye MS SQL gurus.

    I have a particularly stubborn tempdb on one of my servers giving me a very large headache. It is currently about 15GB in size, mostly empty space. No matter what I do I cannot seem to shrink it.

    I saw a thread from back in July where someone was having the same problem and I tried all the suggested remedies with no luck. There are no open transactions that I can see. I tried restarting SQL, no luck. I disabled all replication jobs, anything I can think of that might have something in tempdb, no luck. This is a development server so there isn't too much traffic, I don't see any locks on any tempdb objects.

    Anyone have any tricks up their sleeve for this sort of thing?

    Server details:

    MSSQL 2000 SP3 Standard Edition

    Win2003 Server Standard Edition

    Thanks!

    Pam



    The greatest obstacle to transforming the world is that we lack the clarity and imagination to conceive that it could be different. -- Roberto Unger

  • February 17, 2005 at 11:15 am

    #542897

    I don't understand have you tried to shrink it?

    Quite simple explanation of doing this in  BOL "How to shrink a database (Enterprise Manager)"  Or maybe I missed something?

  • February 17, 2005 at 12:28 pm

    #542913

    Yes, sorry if that wasn't clear. I've tried to shrink it several times using Enterprise Manager and Query Analyzer. It always says the shrink was successful but it never actually shrinks it at all.



    The greatest obstacle to transforming the world is that we lack the clarity and imagination to conceive that it could be different. -- Roberto Unger

  • February 17, 2005 at 1:27 pm

    #542927

    tempdb gets re-created everytime SQL server starts up.

    You can change the location of tempdb by specifying alter database tempdb modify file name='tempdb',filename = '' and then re-start sql services

    or

    Stop sql services and delete the old tempdb files and start SQL services again.

    It might start up with the original size but you should be able to shrink the files now.

  • February 17, 2005 at 1:40 pm

    #542933

    I stopped SQL, deleted the files, restarted SQL. The new tempdb was created at 15GB (completely empty) and it still won't shrink via EM or QA.



    The greatest obstacle to transforming the world is that we lack the clarity and imagination to conceive that it could be different. -- Roberto Unger

  • February 17, 2005 at 2:09 pm

    #542947

    TRY THIS

    HTH


    * Noel

  • February 17, 2005 at 2:46 pm

    #542959

    Ah, thank you Noel! Method 1 was the only one I hadn't tried and it worked.

    Much obliged!

    ~Pam



    The greatest obstacle to transforming the world is that we lack the clarity and imagination to conceive that it could be different. -- Roberto Unger

  • February 17, 2005 at 3:01 pm

    #542964

    You are welcome


    * Noel

  • February 23, 2005 at 4:40 pm

    #543807

    KB 307487 works a treat!   I had the same issue a MOM Reporting Services instance chewing up TEMPDB.

    Brian 

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

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