Increase ecessivo tempdb

Question

Increase ecessivo tempdb

rmedeiros

SSC Enthusiast

Points: 134
More actions
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.

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

rmedeiros SSC Enthusiast Points: 134 More actions · Answer 2

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

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)

JManuelN SSCommitted Points: 1708 More actions · Answer 5

Try this query to identify the problem

TempDB usage per active session

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

rmedeiros SSC Enthusiast Points: 134 More actions · Answer 6

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.