tempdb has grown x16 in a few hours

Question

Post reply

tempdb has grown x16 in a few hours

Mike Gray-474150

SSC Eights!

Points: 997
More actions
June 1, 2009 at 8:37 am

#130908

I noticed that my tempdb on SQL 2005 has grown from 982MB to 17GB today. How can I tell what is causing the growth? It appears to stopped growing but now I'm wondering if it will shrink on its own or do I need to run a dbcc shrinkdatabase against it?

Viewing 9 posts - 1 through 8 (of 8 total)

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

Here are my two cents.

You can check your errorlog to check whether or not you can catch any activities.

Whether or not you need to shrink tempdb depends on your database setting.

Mike Gray-474150 SSC Eights! Points: 997 More actions · Answer 2

nothing much in the error logs and the tempdb is not set to Auto Shrink. It looks like I just need to run a shrink against it. Should I set Auto Shrink to true for the tempdb? Is this how most people set it?

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 3

I generally don't auto shrink any databases....

Temp db was obviuosly under a heavy load, that forced it to grow, with autoshrink turned off the TEMPDB will remain at its current size until you shrink it, the sql service is restarted or it needs to grow again.

My guess is you had a process of some kind running that used alot of TEMPDB.

This Microsoft article discusses the process of shrinking TEMPDB:

http://support.microsoft.com/kb/307487

just be aware that it will grow again the next time the process that caused it to grow runs again.

Gethyn Elliswww.gethynellis.com

CrazyMan SSChampion Points: 12560 More actions · Answer 4

Hi Mike

First i would check the process that are active to see if there are any ad-hoc process is in place, by stopping this will help you stop the growth in first place and then see how to shrink them.

As said earlier, temp DB grows in size with transactions

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

Assuming you have 'sa' privileges, you can query tempdb and see what's causing the growth.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Mike Gray (6/1/2009)
nothing much in the error logs and the tempdb is not set to Auto Shrink. It looks like I just need to run a shrink against it. Should I set Auto Shrink to true for the tempdb? Is this how most people set it?

No, don't set this for any of your databases.

Tempdb growth can be caused by a number of things. Did you do any index maintenance operations recently on a database that is fairly large and the index rebuild couldn't occur in memory, or you had the sort in tempdb option set for it? did you have code run with a bad Cartesian product as the result?

EllieDBA SSCommitted Points: 1786 More actions · Answer 7

You might want to run sp_who2 and see if there is a spid (a posible bulk insert) that is running too much of your resources and kill it.

hope that helps

Glenn Dorling SSCertifiable Points: 7876 More actions · Answer 8

It's really hard to tell what caused tempdb growth retrospectively: you really need to be checking while it's growing (of course, the question is then how do you predict that).

If you're lucky it was an SQL Agent job and you'll be able to track down what was running at the time tempdb grew.

The odds are though that it was an interactive query, for which there'll be no real way of identifying what was going on.

There's an enormous number of potential reasons for tempdb growth, but the most likely things to cause it are reindexing or other sort operations, joins ( especially between multiple and/or large tables, and especially cartesian products), sub-queries, snapshot locking of some description, remote queries (eg. via a linked server), not to mention explicit temporary objects.

Is 17GB that big really? If your databases are 10GB it is: that would point to a cartesian product query having been run. However, if your databases are 100GB or larger with tables of 10+GB then a 17GB tempdb isn't necessarily big: in this case it would be best to permanently increase its size.