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?
GMacherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveH_UKCommented:
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.
0
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?
0
NightmanCTOCommented:
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?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

SteveH_UKCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveH_UKCommented:
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.
0
GMacherAuthor Commented:
Regrettably we have yet to find the initial cause of the rapid growth however it has yet to happen again.
0
SteveH_UKCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.