none
How we can reduce Temp DB size with out restart Server

    Question

  • 1. How we can reduce Temp DB size with out restart Server?

    2. In SP if we use any temp table & later if those table not drop explicitly then what will happen?? is those table will be available in temp table .. i mean it will occupy the space??

     

    Waiting for expert suggestion.

     

    Wednesday, June 22, 2011 11:28 AM

Answers

  • If there are no activities in tempdb, you can run DBCC SHRINKFILE
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Peja Tao Friday, June 24, 2011 5:03 AM
    • Marked as answer by Peja Tao Wednesday, June 29, 2011 9:00 AM
    Wednesday, June 22, 2011 11:53 AM
  • 1. How we can reduce Temp DB size with out restart Server?

    2. In SP if we use any temp table & later if those table not drop explicitly then what will happen?? is those table will be available in temp table .. i mean it will occupy the space??

     

    Waiting for expert suggestion.

     


    Start from here - http://support.microsoft.com/kb/307487 and http://msdn.microsoft.com/en-us/library/ms175527.aspx
    Thanks, Leks
    • Proposed as answer by Peja Tao Friday, June 24, 2011 4:57 AM
    • Marked as answer by Peja Tao Wednesday, June 29, 2011 9:00 AM
    Wednesday, June 22, 2011 9:00 PM

All replies

  • If there are no activities in tempdb, you can run DBCC SHRINKFILE
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Peja Tao Friday, June 24, 2011 5:03 AM
    • Marked as answer by Peja Tao Wednesday, June 29, 2011 9:00 AM
    Wednesday, June 22, 2011 11:53 AM
  • After running Shrink command i am not finding any big difference in temp database.

     

    IS temporary table automatically dispose from temp DB or we need to explicitly need to drop ???

     

     

    Wednesday, June 22, 2011 12:18 PM
  • Since SQL Server 2005 you do not have to drop a temporary table , it is cached ....
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, June 22, 2011 12:22 PM
  • what about SQL server 2008?
    Wednesday, June 22, 2011 12:36 PM
  • Same, since SQL Server 2005 and onwards
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, June 22, 2011 12:45 PM
  • So IS it drawback of SQL server Space management ? or there have some specific reason behind it.
    Wednesday, June 22, 2011 1:10 PM
  • 1. How we can reduce Temp DB size with out restart Server?

    2. In SP if we use any temp table & later if those table not drop explicitly then what will happen?? is those table will be available in temp table .. i mean it will occupy the space??

     

    Waiting for expert suggestion.

     


    Start from here - http://support.microsoft.com/kb/307487 and http://msdn.microsoft.com/en-us/library/ms175527.aspx
    Thanks, Leks
    • Proposed as answer by Peja Tao Friday, June 24, 2011 4:57 AM
    • Marked as answer by Peja Tao Wednesday, June 29, 2011 9:00 AM
    Wednesday, June 22, 2011 9:00 PM