Post reply

Increase ecessivo tempdb

  • July 22, 2015 at 6:33 am

    #302610

    Good morning everyone, I have a problem in my SQL and would like a help from you, my tempdb bank in recent weeks is with a ecessivo growth is coming almost 500GB at the end of the day, I'm having to do the shrink all days to have no problems with slowdowns during the day, is there any way for me to identify the cause,obs. is not the log, wait.

  • July 22, 2015 at 7:06 am

    #1814094

    You need to review the processes running that increase the size of TempDB. Probably large loads that need ordering, index rebuilds or bad queries. You could use extended events to track this issues down.

    Before reducing the size of TempDB daily, you need to correct this issues or you'll slow down your database by forcing TempDB to grow everyday.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 22, 2015 at 9:38 am

    #1814149

    Luiz thanks for the return, I'm looking for help on the forums, I'm not picking up random script, I have many databases with a total data 7TB can not run any script without making a first analysis, there are many customers who will be affected

  • July 22, 2015 at 9:43 am

    #1814150

    rmedeiros (7/22/2015)

    Luiz thanks for the return, I'm looking for help on the forums, I'm not picking up random script, I have many databases with a total data 7TB can not run any script without making a first analysis, there are many customers who will be affected

    I suppose that you're saying that to respond to my signature. That's basically a disclaimer which appears on all my posts and not something specific for you.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 22, 2015 at 3:43 pm

    #1814240

    rmedeiros (7/22/2015)

    Good morning everyone, I have a problem in my SQL and would like a help from you, my tempdb bank in recent weeks is with a ecessivo growth is coming almost 500GB at the end of the day, I'm having to do the shrink all days to have no problems with slowdowns during the day, is there any way for me to identify the cause,obs. is not the log, wait.

    This is typically because of an accidental CROSS JOIN otherwise known as a many-to-many join. It's almost always in a query that uses DISTINCT or GROUP BY to help eliminate the duplicates that are spawned.

    You need to find this query and fix it. Right click on the instance in the Object Explorer and click down through "Reports" to find the performance report for the most IO. The top offender will be the one you're looking for, in this case.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 23, 2015 at 4:22 am

    #1814315

    Try this query to identify the problem

    TempDB usage per active session

    http://qa.sqlservercentral.com/scripts/tempdb/72007/[/url]

  • July 23, 2015 at 9:54 am

    #1814443

    I would like to thank everyone for the help, I am still analyzing the environment, the problem is not yet solved, but you guys have helped me a lot.

Viewing 7 posts - 1 through 6 (of 6 total)

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