Temp DB Issue

Question

Temp DB Issue

DBABC

SSCarpal Tunnel

Points: 4813
More actions
February 11, 2010 at 1:45 pm

#100712

We have SQL 2000 instance and Temp DB is going insane on it. We restrict the max size of data file of Temp DB to 4GB but soon it reaches to max and start giving us error that "data file is full", and if we remove the restriction it will shoot up to 21-22 GB and taking up all the space on the disk.
Why temp db is not automatically shrinking or reusing the space? How can we configure it to do so?
Thanks
UT

Viewing 12 posts - 1 through 11 (of 11 total)

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

RP_DBA SSCertifiable Points: 5116 More actions · Answer 1

Have you checked for any long running processes that would be affecting the tempdb? We had a similar experience with the tempdb growing until it filled up the disk - a block of code that was populating a table variable in an infinite loop. Since the process never finished, the tempdb never had a chance to purge itself.

_____________________________________________________________________
- Nate

@nate_hughes

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 2

Thanks for the response RP, but no such thing in our case.

I used following queries to check the row count for each object in tempdb and the open trans, but nothing there.

SELECT OBJECT_NAME(id), rowcnt

FROM tempdb..sysindexes

WHERE OBJECT_NAME(id) LIKE '#%'

ORDER BY rowcnt DESC

DBCC OPENTRAN('tempdb')

GTR SSCrazy Eights Points: 8007 More actions · Answer 3

Short term solution: Is to take backup and see if it frees log file if Recycle SQL Services.

Long Term Solution: Make sure TempDB is set to autogrowth and DO NOT set maximum size of the database. Check books online for more TempDB settings.

EnjoY!

EnjoY!

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

UT- (2/12/2010)
Thanks for the response RP, but no such thing in our case.
I used following queries to check the row count for each object in tempdb and the open trans, but nothing there.
SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC
DBCC OPENTRAN('tempdb')

You really need to check Profiler for things that cause TempDB growth... indexes and temp tables aren't the only thing that will cause such growth. Accidental cross joins are actually quite common and are a typical cause of TempDB going nuts.

--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)

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 5

Jeff Moden (2/12/2010)
Accidental cross joins are actually quite common and are a typical cause of TempDB going nuts.

Thanks Jeff, can you please give me an example of "Accidental" cross join? This will be just for my knowledge as i never came accross any such join.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 6

UT- (2/12/2010)
Jeff Moden (2/12/2010)
Accidental cross joins are actually quite common and are a typical cause of TempDB going nuts.
Thanks Jeff, can you please give me an example of "Accidental" cross join? This will be just for my knowledge as i never came accross any such join.

Sure... but I'll bet you've come across such a join many times and didn't know it.

Another name for an "accidental cross join" is a "many to many join". A lot of times, people aren't aware of the condition of data in their tables and either don't include enough join criteria or it's not possible to apply enough join criteria in the single query. This is especially true when they use complex joins to do aggregations but the problem can also occur without aggregations being present. In those cases, people will use DISTINCT to control the output of the entire query. Behind the scenes, though, the system still has to "touch" all of the many to many rows and most of that work (which is a huge amount of work) is done in "working" tables which live in TempDB which, in turn, causes TempDB to grow a huge amount.

Once you identify where a many to many join is happening in a query (such queries usually have lot's of joins because a lot of folks think that "set based" means "all in on query"... it doesn't, by the way.), you can usually get some remarkable performance improvements by using a TempTable to hold only the rows you need and join on that instead of the many to many relationship. Such "accidental cross joins" are usually pretty easy to spot in the execution plan... they usually show up as very fat arrows with ridiculously high row counts (Millions and sometimes Billions).

As an anecdote, a couple of jobs ago, my boss' boss asked me to setup a new instance of SQL Server so they could run the infamous "year-end query". To make a very long story short, the query had such an accidental cross join in it that would slam 4 cpu's against the wall for 45 minutes, saturate the I/O system, and cause TempDB to grow to over 80GB. The previous DBA used to simply do what they said and created the new instance (good thing he was in NY or there would have been a 7 course pork chop dinner involved).

I analyzed the query, isolated the correct distinct information in a Temp table using a simple SELECT with the correct criteria and a GROUP BY (in this case because aggregation was involved, as well), and then joined to that. The report ran so fast (< 8 seconds) they thought it didn't run. It didn't show up on TaskMgr as anything more than a 10% blip about half a second long. Most of the total duration was just to display the result set.

It didn't take that long to find the problem and redact the code, either. It only took about 30 minutes. The thing that took the longest was convincing them to wait the 30 minutes instead of spending the time to setup a new instance... I was new on the job and they were still "non believers". 😛 Because of all the data involved to create the report, they simply didn't think that any amount of code rework would make a difference.

As a side bar, I sometimes call this technique "Divide'n'Conquer". Peter Larsson has an even more appropriate name for the technique... he calls it "Pre-aggregation" (whether aggregations are actually involved or not). Whatever you decide to call it, it's a very, very effective technique and it's helped me turn many a server crippling, fire breathing monster of a query into nothing more than a blib.

The key is finding where you need to do that and finding nasty fat arrows in the execution plan is normally one of the better indications of where to look in your code.

--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)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 7

As yet another side bar, an "accidental cross join" can also be in the form of what is known as a "Triangular Join". Take a look at the following article to see what those look like. They're especially deadly when used in an ON or WHERE clause. I'll just bet you've seen these before and not recognized them for the server crippling, TempDB exploding problem they actually are, either.

[font="Arial Black"]Hidden RBAR: Triangular Joins[/font][/url]

--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)

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 8

DBABC

SSCarpal Tunnel

Points: 4813

February 13, 2010 at 1:51 pm

#1118489

Nice article, thanks Jeff.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 9

You bet... thanks for the feedback, UT.

--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)

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 10

Thanks for sharing your experience Jeff.

Many thanks!

MJ

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 11

You bet. Thanks, MJ.

--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)