December 7, 2009 at 10:24 am
Since 2 week the tempdb on one of my server is growing abnormally. Once it growth 200GB in 35 minutes. Since then I have set an alert to let me know when the database reaches a threshold, but I can’t find what transaction is the problem. Each time I connect to verify after the alert, the process is gone. What I can do to find what is causing the tempdb to growth?
Thanks
Rem
December 7, 2009 at 11:02 am
Sounds to me like you will need to set up a server-side trace on the system. Others may be able to help you off the top of their heads as to what events to look at in the trace, I can only suggest doing some reading in BOL (Books Online, the SQL Server Help System).
December 7, 2009 at 11:11 am
Well, if it's a single query growing the tempDB to 200GB, I assume it takes a long time to run. You could run: SELECT max_logical_writes, qt.text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY Max_Logical_Writes DESC
December 8, 2009 at 8:19 am
Index rebuilds if the use tempdb attribute is on then that could be the issue. While trying to find the problem is certainly important, you could also choose to reset the initial size of tempdb to larger to help to prevent the growth. Also make sure growth is changed to a fixed amount instead of a percentage.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 8, 2009 at 8:44 am
I'll second Jack's advice. You might want to set tempdb larger to start with to handle this, set up a trace, check in maintenance operations. If this is during the day, I hope you don't have indexing going on. If it's a query, a trace should catch it. look for something using a lot of resources, I'd assume a high duration.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply