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
    sudoku, Nov 13, 2006
    #2

  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
    ghemant, Nov 13, 2006
    #2

  3. gk_sql New Member

    Hi,

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

    gk_sql, Nov 13, 2006
    #2

  4. gk_sql New Member

    Hi,

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

    Gopi
    gk_sql, Nov 14, 2006
    #2

  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
    sudoku, Nov 14, 2006
    #2

  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

    gk_sql, Nov 14, 2006
    #2

  7. xuka200308 New Member

    run this:

    DBCC SHRINKDATABASE (tempdb, 10)
    GO


    you can run it a couple of times if necessary.
    xuka200308, Nov 14, 2006
    #2

Share This Page