TempDB rapidly grew out of control - Expanded to fill disk

I have a brand new Windows 2003 R2/SQL 2005 cluster which is being TEMPeramental...

The Servers are connected to a 500GB iSCSI SAN which is the shared drive to allow for SQL failover.  10 databases (250mb each) were added to the server on a Friday and by Sunday night the TEMPDB grew from the 8mb initial to over 490GB -

These same databases and applications were running on a previous SQL server that was 2005 but was standalone. I checked the settings for the TEMPDB on the original server as well as the new cluster and both were setup identically. Recovery model is Simple, Auto Grow at 10%. The original server had many more databases and never grew to more than 400mb - Has anyone ever seen a runaway tempdb like this?
Who is Participating?
SteveH_UKConnect With a Mentor Commented:
Have a look at this article: http://www.simple-talk.com/sql/database-administration/tracking-tempdb-growth/.

Changing the size of (shrinking) tempdb is difficult without restarting the instance, especially if there is a lot of activity.

I'd recommend that you look at SQL's history of missing indices (which is cleared when the instance starts) (see http://blogs.bdnet.co.uk/steve/archive/2008/01/11/SQL-Server-Missing-Indices-and-a-Helpful-Database.aspx) and also do a performance trace.

If you are doing any DBCC jobs, these should be replaced with 2005 equivalents.

Also, I'd still recommend checking for index problems even if this database has been moved.  If they aren't there then you have your problem, and it is an easy check.
Sounds like you probably have runaway queries or no indices on big tables.

The tempdb database is used for sorting and building result sets.  If the appropriate indices aren't available, SQL has to put the results into tempdb and then correlate and sort them there.

I suspect you may have some users attempting to join tables without appropriate joins or without indices.
GMacherAuthor Commented:
Steve, Thanks for the response. Im not sure that this is the case only because these databases have been running on the original SQL server for over a year with no such instance and once moved to a cluster and with no development work occuring over the weekend, they were pretty much just running as they were before.

Since then it hasnt grown out of control but it did hit the 200mb limit that was set on Monday (28th) after the initial repair; the following Thursday (31st) - Of course since it was limited and doesnt appear to be auto truncating none of the database were accesible until the SQL service was restarted, at which point I bumped the limit to 4 GB which I think excessive but I figured just incase.

I checked for open transactions but since I had to restart the service to get a response I lost any valueable data that might have been able to tell me what it was. So I guess if I cant pin point whats causing it just yet, can I at least ensure that the TEMPDB is auto truncated when a certain % of the alloted space is used?
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

1. Have you set the db compatability to 90, or is it still on 80?
2. Are you running any database integrity checks on these large databases (such as DBCC CHECKDB) - this is a *heavy*tempdb user.
3. Are you rebuilding indexes with any regularity?
Glad to help. Do let us know what solved your particular problem as it will help us to interpret symptoms correctly for other users.  Thanks.
GMacherAuthor Commented:
Regrettably we have yet to find the initial cause of the rapid growth however it has yet to happen again.
Did you do a bulk import at any point?  Bulk imports can cause growth in tempdb if the order of the import doesn't match a clustered index on the table being imported to.

Anyway, sorry to hear you didn't find the cause.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.