Post reply

Temp Database is huge

  • December 18, 2015 at 7:29 am

    #399485

    I need help here please,

    I notice my tempdb is grown to 42 GB.

    I want to shrink the size, we do not use transaction log for recovery purposes,

    all my databases are in simple mode,

    Please Advise,

    Regards,

    Bubby

  • December 18, 2015 at 7:42 am

    #1846774

    Find the queries that are doing hash matches and rewrite them.

    Has someone done something silly like create a temp table which holds Gigs of data?

    Shrinking temp db may only be temporary 🙂 (tempception) since there may be a recurring workload that may again cause it to grow.

    Also, is the size that much of a problem?

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • December 18, 2015 at 7:48 am

    #1846777

    The drive is getting almost max due to that, How to troubleshoot please? can I be assisted with some queries please

  • December 18, 2015 at 9:22 am

    #1846819

    Tempdb grows to the size needed based on your workload. However the space is re-used in tempdb. If it continues to grow, your workload is too large for your tempdb sizing, or perhaps you have some long running or open transaction that limits what space can be re-used.

    You can use sp_WhoIsActive to help you check for usage: http://www.littlekendra.com/2011/02/01/whoisactive/

    You might also monitor tempdb usage, and try to determine if it's an issue. Here's another ref to read: http://www.brentozar.com/archive/2011/10/how-tell-if-tempdb-performance-problem/

    Unfortunately it's not a simple matter to find out why tempdb is growing when every database and every user can affect it.

  • December 18, 2015 at 12:20 pm

    #1846864

    Thanks a Lot, it helped to understand, much appreciated,

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

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