none
TempDB is growing to HUGE size

    Question

  • Hi,

    In our sharepoint installation, we observe that the tempDB is growing continuously and now it has reached nearly 500 Gigs and it continues. Why is this happening? Generally, tempdb should get cleaned up when the temp objects move out of scope. It is not happening so. Can someone let me know if there is anything that need to be done on this side from the SharePoint perspective. What I came to know from various blogs is that TempDB is extensively used in a SharePoint context.

    Please help me in resolving this issue.

    Regards,


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Thursday, August 18, 2011 4:26 AM

Answers

  • Hi,

     

    The tempdb database is a workspace for holding temporary objects or intermediate result sets. It also fills any other temporary storage needs. The tempdb database is re-created every time SQL Server is started.

     

    The size of the tempDB database goes both up and down quickly. Size depends on how many users are using the system, in addition to the specific processes that are running; for example, online rebuilds of large indexes, or large sorts cause the database to grow quickly.

     

    Firstly, the problem could be that because your database is restricted from growing any further.  You need to remove the growth restriction from your database and rerun the wizard.

     

    Secondly, check if some databases have errors, if so, repair the database with DBCC CHECKDB.

     

    Thanks,

    Rock Wang


    Regards, Rock Wang Microsoft Online Community Support
    Wednesday, August 24, 2011 7:18 AM

All replies

  • Hi Phani.

    The temp DB is used for more or less everything in SharePoint and is used as a temp storage before data beeing allocated to the proper DB. The size of a TempDB is as far as I have managed to understand growing as needed and is not autoshrinking(should not and cannot be). This would mean that in your sustem the DB has grown at some occation to a size of 500GB (large yes but that depends on the circumstances) and it will keep this size until SQL is restarted and the TempDB rebuilt. Perhaps it will then again reach this size if needed by the system.

    If you have for example a max upload limit of 1GB and many users(500) upload at the same time, then the tempDB would grow large, since it is not shrinking automatically, it would stay at the peak size. If that size is really needed then it would be recommended in my opinion to plan for a large TempDB, the performance of it can be improved in many ways:

    Optimizing TempDB Performance

    Capacity Planning for TempDB

    The main thing of it all though, the Temp DB does not shrink, even after the actual data is removed...

     

    Help any?

    Regards


    Thomas Balkeståhl - Technical Specialist - SharePoint
    Thursday, August 18, 2011 6:46 AM
  • Hi Thomas,

    Thanks for your quick response. It is surprising to see this point that tempdb doesnt get freed up automatically. Restarting SQL Server would be the last option that I can think of as it needs down time in the production and need to go through lot of gates to get this through.

    Request you/any other geeks who can help me out with a solution to this case other than restarting server. I already had a look at the above links that you have shared.

    Regards,


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Thursday, August 18, 2011 10:27 AM
  • Experts! please help us.
    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Wednesday, August 24, 2011 4:26 AM
  • Is the SQL Server used only by SharePoint?

    Have you developed any Custom Solutions on this SharePoint using Visual Studio?


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

    The SQL Server is used only by SharePoint. Yeah we do have a custom solution where we use it for collaboration across our team which has around 150 members.

    Regards,


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Wednesday, August 24, 2011 5:40 AM
  • So, did this TempDB growth problem poped up since Day 1? or after making any new deployment/changes to the SharePoint?
    Thanks & Regards,
    Kamlesh | Blog | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, August 24, 2011 5:59 AM
  • It was a general observation. The growth started over a period of time and now the size of tempDB comes to almost 500 GB approx. There are no typical deployments made to the sharepoint.

    Regards,


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Wednesday, August 24, 2011 6:03 AM
  • Hi Phani,

    The 1st post says 500 GB and now you mention 170GB. 

    Kindly keep monitoring the growth of the DB closely on daily basis, till other experts also share their response on this thread.

     


    Thanks & Regards,
    Kamlesh | Blog | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, August 24, 2011 6:06 AM
  • Sorry! 500 Gigs is the correct size. Got it confirmed. It was my mistake.
    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Wednesday, August 24, 2011 6:09 AM
  • Hi,

     

    The tempdb database is a workspace for holding temporary objects or intermediate result sets. It also fills any other temporary storage needs. The tempdb database is re-created every time SQL Server is started.

     

    The size of the tempDB database goes both up and down quickly. Size depends on how many users are using the system, in addition to the specific processes that are running; for example, online rebuilds of large indexes, or large sorts cause the database to grow quickly.

     

    Firstly, the problem could be that because your database is restricted from growing any further.  You need to remove the growth restriction from your database and rerun the wizard.

     

    Secondly, check if some databases have errors, if so, repair the database with DBCC CHECKDB.

     

    Thanks,

    Rock Wang


    Regards, Rock Wang Microsoft Online Community Support
    Wednesday, August 24, 2011 7:18 AM
  • I dont think we got this resolved. We have checked both of what you suggested. The databases were not configured to be restricted growth not the databases have errors.

    Still the TempDB size grows.


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Friday, August 26, 2011 4:00 AM