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?

  • 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

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?

  • 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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