We help IT Professionals succeed at work.

Need help with performance database

VBBRett
VBBRett asked
on
Medium Priority
219 Views
Last Modified: 2012-06-21
I need help with a SQL 2005 database.  The problem that I encounter is that the database keeps on growin in the TEMP file and gets larger and larger.  The TEMP database file is not suupposed to get so huge.  It is currently at 44 gigs.  How am I supposed to figure out what is wrong with the database and why it gets so massive?  What can I do to stop it?
Comment
Watch Question

Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT

Commented:
Are you using a lot of temp tables in your code ?  

Author

Commented:
No.  It appears that there are many transactions happening and thus the reason why so much space is being taken up..but how do I find out what is going on?  How do I fix this issue?
Database Architect - Dba - Data Scientist
CERTIFIED EXPERT
Commented:
<<It appears that there are many transactions happening and thus the reason why so much space is being taken up.>>
Transaction volume does not play as much on temp data files as they would on log file unless there is heavy usage of temporary objects .

What is your database size ?  Are you using row versioning ?  What makes say that it should not get so big ?  I'd recommend a profiler trace filtered on TEMPDB to see what processes write so much o TEMPDB.
deightonprog
CERTIFIED EXPERT
Commented:
http://www.simple-talk.com/sql/database-administration/tracking-tempdb-growth/

he mentions above, if there is a very large join occurring, that might impact on TempDB

here's a query to investigate expensive queries

http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/

Commented:
If you have so much going on in the tempdb think about having multiple files for your tembdb.

When does your temp-db grows? How's your index maintenance (sort_in_tempdb)?  
Try to pinpoint where the things that use tempdb (profiler as racimo suggest). Here an overview of what uses tempdb
http://www.mssqltips.com/sqlservertip/1432/tempdb-configuration-best-practices-in-sql-server/

Author

Commented:
So I found out from Microsoft that there is a query that is drawing a lot of resources while it is compiling.  If that is the case, I was thinking about calling the vendor whom made the software which is hitting TempDB with all the resource eating tasks.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.