May 25, 2011 at 11:10 pm
Suddenly Tempdb growth increased morethan 10gb within few min..that time what are the precausion to be taken
to reduce the tempdb size.
May 26, 2011 at 1:36 am
You can use this script to capture tempdb usage per active session and find the offending query:
http://www.sqlservercentral.com/scripts/tempdb/72007/
Probably one of your queries is not properly tuned and spills internal objects to tempdb. Usually this happens when there is not an appropriate index or statistics are badly outdated. Parallelism can also be a great tempdb consumer: if this is an OLTP database, you can consider lowering your MAXDOP.
You can set up a job to capture tempdb usage and save it to a table every 5 minutes, until you find the offending query and tune it.
Hope this helps
Gianluca
-- Gianluca Sartori
May 26, 2011 at 4:23 am
i had size issues with temp db too, it was down to the application using alot of temp. tables.
May 26, 2011 at 4:53 am
Another person just recently had an issue where someone posted millions of rows and was doing sorts on unindexed columns while they did so, smacked tempdb good and hard. It's down to the queries that are running on your system and what they're doing. It could be temp tables or table variables or sorts or it could be more arcane stuff like triggers or join criteria (hash tables)... There are lots of reasons for tempdb to grow. Follow Gianluca's advice.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply