Post reply

tempdb got big

  • September 8, 2011 at 10:19 am

    #1378721

    Ninja's_RGR'us (9/8/2011)

    I'm not 100% sure here but I really don't think that tempdb is a perfect copy from model.

    Interesting question. One possible answer here:

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/14/does-tempdb-get-recreated-from-model-at-startup.aspx

    -- Gianluca Sartori

  • September 8, 2011 at 10:22 am

    #1378727

    Gianluca Sartori (9/8/2011)

    Krasavita (9/8/2011)

    I get one result, can you explain to me,how I can understand what I should get to know that is is bad for tempdb

    High values for "internal object MB space" mean that the statement is using lots of space for internal objects.

    Reason? Often a bad plan. See if you can tune it.

    I would hardly call 100 mb of huge amount for 100 + GB tempdb. Way less than 1% of the ressources.

    I'm not saying it couldn't be improved but I wouldn't lose sleep over that one. Not the first step to debug here!

  • September 8, 2011 at 10:25 am

    #1378730

    Gianluca Sartori (9/8/2011)

    Ninja's_RGR'us (9/8/2011)

    I'm not 100% sure here but I really don't think that tempdb is a perfect copy from model.

    Interesting question. One possible answer here:

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/14/does-tempdb-get-recreated-from-model-at-startup.aspx

    Pretty much what I was thinking (made the most sense).

    Thanks for the link.

  • September 8, 2011 at 10:37 am

    #1378748

    Ninja's_RGR'us (9/8/2011)

    Gianluca Sartori (9/8/2011)

    Krasavita (9/8/2011)

    I get one result, can you explain to me,how I can understand what I should get to know that is is bad for tempdb

    High values for "internal object MB space" mean that the statement is using lots of space for internal objects.

    Reason? Often a bad plan. See if you can tune it.

    I would hardly call 100 mb of huge amount for 100 + GB tempdb. Way less than 1% of the ressources.

    I'm not saying it couldn't be improved but I wouldn't lose sleep over that one. Not the first step to debug here!

    Sorry, where did that 100 MB come from? I don't see that number anywhere in this thread.

    -- Gianluca Sartori

  • September 8, 2011 at 10:42 am

    #1378757

    He edited his post, it's the one prior to this one. There was 2 rows for 200 MB so I just rounded :-D.

    Ninja's_RGR'us (9/7/2011)

    That's 200 MB, not the issue.

    What have you done so far?

  • September 8, 2011 at 10:58 am

    #1378771

    I re started server and my temp db got very 111.38 MB small, but I would like to monitor and see why it got so big before.

  • September 8, 2011 at 11:03 am

    #1378775

    Krasavita (9/8/2011)

    I re started server and my temp db got very 111.38 MB small, but I would like to monitor and see why it got so big before.

    We told you 19 837 738 different ways to do it (actual number).

    Here's the best one : download the sql monitor trial (2 X 14 days). That'll will let you debug this to death.

  • September 9, 2011 at 1:47 am

    #1379049

    Ninja's_RGR'us (9/8/2011)

    He edited his post, it's the one prior to this one. There was 2 rows for 200 MB so I just rounded :-D.

    Ninja's_RGR'us (9/7/2011)

    That's 200 MB, not the issue.

    What have you done so far?

    Ah, thanks.

    -- Gianluca Sartori

  • September 9, 2011 at 8:34 am

    #1379290

    Hello, my temp is getting bigger,I have now 381 mb. I just installed sql monitore,but this tool is not providing why temp db is getting better.

    Any ideas what I should look for?

    Thank you

  • September 9, 2011 at 8:40 am

    #1379296

    Krasavita (9/9/2011)

    Hello, my temp is getting bigger,I have now 381 mb. I just installed sql monitore,but this tool is not providing why temp db is getting better.

    Any ideas what I should look for?

    Thank you

    The idea behind this tool is that if saves WHEN it happens and you can see it in the database section.

    Then you can match that against whatever was running at that time.

    Make sure to turn on the trace option and select never deletes on all traces so you keep all the info untill you debug this.

    The trial extension key is => I need more time

    Enter it on the 14th day, not before because it's not cumulative, it just resets it to 14 days.

    Keep working on the sames projects as you did in the last few days / weeks so that you likely hit the same problematic query(ies).

    I would expect to get a long running query alarm close to the same time you get massive tempdb growth.

    Either that or a job running at the same time to reindex or import massive data.

    Last option is an accidental cross join + distinct or group by. It might return like 10 runs but need to generate 1 trillion rows in the middle of the execution.

    That's all I can offer without doing it for you.

  • September 9, 2011 at 1:45 pm

    #1379496

    Thank you, for the trace when I start running, what options should I check for?

  • September 9, 2011 at 6:23 pm

    #1379546

    Krasavita (9/9/2011)

    Thank you, for the trace when I start running, what options should I check for?

    SM will take care of that for you. Just ask it to save the trace and NOT delete them.

  • September 9, 2011 at 6:53 pm

    #1379549

    Where is that option located thank you agai

  • September 9, 2011 at 6:59 pm

    #1379550

    Configuration, trace

  • September 12, 2011 at 3:27 am

    #1379845

    use command dbcc opentran to know the current open transaction.

Viewing 15 posts - 31 through 45 (of 47 total)

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