February 29, 2008 at 12:04 am
What would be the best action to take when you see that the size of your temp DB is growing abnormally?
Thanks
February 29, 2008 at 2:54 am
may sound simple but try and identify what is casuing the growth of tempdb
Gethyn Elliswww.gethynellis.com
February 29, 2008 at 8:54 am
Check if any application is using more hassh tables and they are plenty of rows in it. They are the usual cause for that.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 29, 2008 at 8:57 am
Also check for open transactions. I've seen those cause issues.
Look for someone building large temp tables as well.
February 29, 2008 at 9:23 am
What do you consider "abnormal"? What is your tempdb spec'd at currently? Are we discussing SQL 2000 or 2005? Optimizing tempdb in 2005 is quite different than 2000.
If you could also give us info about the server itself it may be pertinent in answering your question (RAM, CPU, etc.)
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 2, 2008 at 10:29 am
One of the main things to note about SQL server 2005 is that it uses the tempdb to handle more operations. What you should be looking for is what is causing the temp db to grow so large. It could be temp tables, large table variables, sorting or aggregate functions etc...
As an interim solution, you could add more files to the tempdb. This will allow for additional space. For best performance, the files should be stored on seperate disks, if possible. If you choose to add more files, you should make all the files have the same default size.
more info on tempdb in sql 2005:
http://msdn2.microsoft.com/en-us/library/ms345368.aspx
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1276989,00.html#
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply