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?
Asked
Active
Viewed 478 times
1
-
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 07 '10 at 04:54
2 Answers
0
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.
0
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 06 '10 at 21:09
-
-
Distinct, sorts that go to tempdb. Hard to pinpoint. How large is it getting? I hardly allocate less than 16gb to tempdb. – TomTom May 07 '10 at 05:11
-
TempDB is only 140MB. The database that seems to cause is it 1.8 GB. – Jason N. Gaylord May 07 '10 at 16:20