Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Even though the log file shows that there is over 1 GB of free space, we start receiving an error message every 3 or 4 days saying that the TempDB file is full. I know cursors impact the TempDB file, but is there anything else I should be looking at to see why this keeps happening? I've tried running SQL Profiler, but when running it, it slowed down the DB so much that the users were experiencing timeouts. What specific items should I check for in SQL Profiler?

share|improve this question

migrated from stackoverflow.com May 7 '10 at 4:54

This question came from our site for professional and enthusiast programmers.

    
Although this does pertain somewhat to DB development, I think you'd be more likely to find a good answer to this on SF, as it's more of a general server performance question as opposed to a specific query/design question. Good luck... –  Aaronaught May 7 '10 at 4:54

2 Answers 2

up vote 0 down vote accepted

Also check your scheduled jobs t osee if one runs right before this happens. A badly designed import or export could do this as well.

ANd why, pray tell, are you running enough cursors to consider that a reason why your tempdb is getting full. Cursors are generally to be avoided espcially on production datbases. WHy aren't you using set-based solutions instead?

share|improve this answer
    
I am converting cursors over to sets, but wasn't sure what else would cause this. Scheduled jobs are happening overnight and this is occurring a few hours after. –  Jason N. Gaylord May 6 '10 at 21:09
    
Anything else that you can think of? –  Jason N. Gaylord May 6 '10 at 21:11
    
Distinct, sorts that go to tempdb. Hard to pinpoint. How large is it getting? I hardly allocate less than 16gb to tempdb. –  TomTom May 7 '10 at 5:11
    
TempDB is only 140MB. The database that seems to cause is it 1.8 GB. –  Jason N. Gaylord May 7 '10 at 16:20

Script out your stored procedures and look for temp tables that are being created but not being dropped. If those sprocs are run frequently you might be filling your tempdb that way.

share|improve this answer
    
I've done that already and I can't seem to find any. –  Jason N. Gaylord May 6 '10 at 21:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.