tempdb growing out of proportion

Question

tempdb growing out of proportion

Shawn Cox

SSC Enthusiast

Points: 139
More actions
October 6, 2005 at 2:37 pm

#166233

Help. For some reason, my tempdb is growing out of control! There is only one application running on this SQL server, which is only 5GB, and the tempdb grows over 25GB!! Sometimes rebooting will fix it, but I usually have to dbcc shrinkfile to get it back down to size.
Running SQL 2000 - just applied SP4 and it happened again today. No pattern, the only thing that consistantly happens is one of the users is running a particularily complicated join statement (through the application) that seems to get hung up. I have contacted the application vendor and not getting anywhere. Any ideas?
SCDBA
SCDBA

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

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

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 1

A few quick ideas, not too fleshed out:

Some user (or scheduled) submitted routine is causing this to happen. SQL Server does not arbitrarily choose to bloat tempdb. Use of temp tables is likeliest.

Use SQL Profiler to determine the commands submitted that are causing the bloat. This is hard, as you need to have the trace in place before the command is submitted--or active when it finishes, whenever that might be. It might pick up activity while the process is running, for example if a function call is embedded in a query. (Pick over the events under "Stored Procedures" and "TSQL".)

While tempdb is growing you could try and analyze who's doing work, or what locks are involved, and/or what objects are being worked with. At the start this means sp_who2 and sp_lock, and chunks of db_id() object_name() and the like; later on, after hacking those system procedures, you can come up with your own queries against the system tables to peer at what's going on "under the hood".

Ultimately, it may just be that the workload generated by your systems requires stupendously large tempdb workspace. Even so, you should always push developers to not write sloppy code, though the best way to do that is to identify the bad code, clearly explain why its bad, and show a (working) superior alternative.

Philip

VD Ten Centuries Points: 1149 More actions · Answer 2

This might not be your case… But I've seen this happen with a query that had a lot of left joins. Once I replaced all of them with not exists (select top 1 1 from ...) the temp db wasn't filling up anymore

barsuk SSChampion Points: 12280 More actions · Answer 3

see if this link can help you: http://support.microsoft.com/default.aspx?scid=kb;en-us;328551

Shawn Cox SSC Enthusiast Points: 139 More actions · Answer 4

Thanks for all your help!

It has not happened again since I posted this, and have been running SQL Profiler when performance allows, hoping to 'catch' it. We thought it might be a custom report generated through the application, but we ran it three times with no problem or tempdb size increase. We are still trying to work with the application vendor - we are 85% sure this occurs through the application where we have no control over the SQL being sent. We have over 2000 users using a single account through the application, so it is not easy to narrow it down. I'll keep you posted and thanks again!

SCDBA