locked
Reduce TempDB size without restarting SQL Server? RRS feed

  • Question

  • I could not get any straight forward answer on this topic - Can we reduce the ever-growing TempDB Size without restarting SQL Server?

    How does one handle the TempDB growth issue on Production Servers?


    Thanks & Regards,
    Kamlesh | Blog | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    • Changed type KSDN Wednesday, August 24, 2011 6:38 AM
    Wednesday, August 24, 2011 6:37 AM

Answers

  • Yes you could theoretically shrink Tempdb Size provided

    1. There are no User Defined Objects occupying tempdb portion of files that are in midst of shrink operation.

    2. There are no internal objects (Hash tables and others) that sql server creates that occupy Tempdb portion of file that is being shrunk.

    3. There are no version stores in part of file that we are trying to shrink.

    4. There are no open transaction in Tempdb Log file.

    5. No Static Cursor in tempdb.

    So repeated ShrinkFile executions, followed by DBCC FREESYSTEMCACHE('ALL') should help use shrink Tempdb files


    Guru | www.ConsultGuru.Me Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    • Proposed as answer by Abhay_78 Wednesday, August 24, 2011 10:12 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, August 30, 2011 10:11 AM
    Wednesday, August 24, 2011 7:17 AM
  • Under most production scenarios, you will not find that TempDB is free of connections, so a scheduled restart of the instance would be your quickest "fix".  A larger question remains though; why do you feel your TempDB is growing too large?  Under typical loads your TempDB will grow to an optimal size and then it will typically level off.  Unless databases on your instance are experiencing frequent ad-hoc queries that push a lot of data into temp objects (which would be cause for unwanted TempDB growth), you may want to resize your disk so it can properly handle the size of TempDB your currently seeing.

    Hope that helps,

    John


    John Eisbrener - http://dbaeyes.wordpress.com/
    Wednesday, August 24, 2011 2:49 PM
  • If there is no activities on tempdb, you can use DBCC SHRINKFILE command.

    ---How does one handle the TempDB growth issue on Production Servers?

    http://technet.microsoft.com/en-us/library/cc966545.aspx

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Abhay_78 Wednesday, August 24, 2011 10:11 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, August 30, 2011 10:11 AM
    Wednesday, August 24, 2011 7:57 AM
    Answerer

All replies

  • Yes you could theoretically shrink Tempdb Size provided

    1. There are no User Defined Objects occupying tempdb portion of files that are in midst of shrink operation.

    2. There are no internal objects (Hash tables and others) that sql server creates that occupy Tempdb portion of file that is being shrunk.

    3. There are no version stores in part of file that we are trying to shrink.

    4. There are no open transaction in Tempdb Log file.

    5. No Static Cursor in tempdb.

    So repeated ShrinkFile executions, followed by DBCC FREESYSTEMCACHE('ALL') should help use shrink Tempdb files


    Guru | www.ConsultGuru.Me Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    • Proposed as answer by Abhay_78 Wednesday, August 24, 2011 10:12 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, August 30, 2011 10:11 AM
    Wednesday, August 24, 2011 7:17 AM
  • If there is no activities on tempdb, you can use DBCC SHRINKFILE command.

    ---How does one handle the TempDB growth issue on Production Servers?

    http://technet.microsoft.com/en-us/library/cc966545.aspx

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Abhay_78 Wednesday, August 24, 2011 10:11 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, August 30, 2011 10:11 AM
    Wednesday, August 24, 2011 7:57 AM
    Answerer
  • Under most production scenarios, you will not find that TempDB is free of connections, so a scheduled restart of the instance would be your quickest "fix".  A larger question remains though; why do you feel your TempDB is growing too large?  Under typical loads your TempDB will grow to an optimal size and then it will typically level off.  Unless databases on your instance are experiencing frequent ad-hoc queries that push a lot of data into temp objects (which would be cause for unwanted TempDB growth), you may want to resize your disk so it can properly handle the size of TempDB your currently seeing.

    Hope that helps,

    John


    John Eisbrener - http://dbaeyes.wordpress.com/
    Wednesday, August 24, 2011 2:49 PM
  • Works fine for me!
    Sunday, November 5, 2017 7:58 PM