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
    sudoku
  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
    ------------------------
    http://hemantgirisgoswami.blogspot.com
  3. gk_sql New Member

    Hi,

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

  4. gk_sql New Member

    Hi,

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

    Gopi
  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
    sudoku
  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.

    Gopi

  7. xuka200308 New Member

    run this:

    DBCC SHRINKDATABASE (tempdb, 10)
    GO


    you can run it a couple of times if necessary.

Share This Page