Tempdb Data file Not Shrinking

  • Hi Expertz,

    I have a scheduled job that contains

    USE master

    DBCC SHRINKDATABASE ('tempdb',0)

    as one of the steps when i check the log i find that this step executed successfully.

    But when i check i find that only templog file to be shrunk and not the tempdev the data file.

    but when i check the tempdb space used

    USE tempdb

    GO

    EXEC sp_spaceused

    i get the attached result.

    What should i do to shrink the tempdb?

    Since its in a scheduled task i dont want to restart the server each time the tempdb files fill up.

    What do you think would have caused the tempdb to grow so much.........

    Please help..........

    Tanx 😀

  • Try with shrinkfile:

    USE TempDB

    DBCC SHRINKFILE('tempdev', 0)

    If it does not work, the TempDB contains objects that are in use by some connection or were not deleted (temp tables for instance).

    For your reference try reading this:

    http://support.microsoft.com/kb/307487/en-us

    Regards

    Gianluca

    -- Gianluca Sartori

  • Ya had tried that

    I killed the processes accessing the tempdb and the shrunk the data file...

    And that worked....

    Is this the case for every other databases should we kill the processes before shrinking the files..............

    Tanx 😀

  • The question to ask yourself is why are you shrinking tempdb? If it grows every day, and you shrink it with a job, you're wasting cycles and disk I/O. Tempdb should be set to the size needed for your server and it shouldn't need to grow or shrink on a regular basis.

  • Do you really need to shrink TempDB at all?

    It has grown because the SQL Server database engine needs to use, either because of explicit temporary objects or because of implicit ones as a result of query joins, sorts, etc. If you shrink it then re-run the same set of queries it's only going to grow again to the previous size.

    The total size of the TempDB data files (the current recommendation from Microsoft is one per processor core up to a maximum of 8) should be set to as large as you expect it to be, so that the files are pre-allocated each time SQL Server starts. If you find you've underestimated the size and they're still growing then increase the defined size of the files.

    The only time I'd consider shrinking TempDB would be if I migrated significant data and/or databases off the server and never expected the remaining databases' use/growth to get up to that approximating the previous situation prior to the data/database migration.

  • The issue was caused by a an sp that was running for a long time which did some indexes and some sortings....

    Couldn't figure out what exactly it does...

    But once i killed the process running the sp i was able to shrink the tempdb file.

    Tanx 😀

Viewing 6 posts - 1 through 5 (of 5 total)

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