SQL Server Performance

Clear tempdb - SQL 2005

Discussion in 'General DBA Questions' started by sudoku, Nov 13, 2006.

  1. sudoku New Member

    As I am running a query to "sort" data, I get a message saying low disk space on Drive.I checked the tempdb it was 8 MB earlier now it shows as 2.5 GB.how do I clear the data in temp db?

    Thx in advance
  2. ghemant Moderator

    Whenever you restart SQL Server it will re-create tempdb.

    Hemantgiri S. Goswami
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  3. gk_sql New Member


    Try restarting Sql Server agent. It will clear the temporary datas in tempdb.

  4. gk_sql New Member


    You can also shrink the tempdb if you cant restart server / agent.

  5. sudoku New Member

    If at all I stop the server ,I have to run the query again .I do not have much space on my drive.Do I have any other option to change the path of the tempdb to another drive(Say drive D) where I have enough space. Can you please explain How do I shrink the tempdb?

    Thx for ur suggestion and thx in advance
  6. gk_sql New Member

    You can use Enterprise Manager --> Tempdb --> All tasks-- >Shrink Database. or
    create one secondary file and point to D: Specify resticted growth to the first file (say 500 mb). Once it reaches the max size, it will automatically redirect to the secondary file.


  7. xuka200308 New Member

    run this:

    DBCC SHRINKDATABASE (tempdb, 10)

    you can run it a couple of times if necessary.

Share This Page