tempdb full with INSERTS?

Hi,
I've got an application that takes data from one table, do certain processes with the data and put it on other tables.

The problem is the data space in tempdb become full at record number 280. I did some test and I did identify that the inserts are the problem, without them the process runs normally with 5000 records.

I'm doing about 4 selects, calling 4 Stored Procedures to verfiy data and i'm doing around 8 inserts per record.

I'll apreciate some help.
Mak042298Asked:
Who is Participating?
 
formulaConnect With a Mentor Commented:
1) Put your inserts in begin-end transactions and split the transactions into smaller "chunks", if possible.
2) Consider increasing the size of your tempdb, as all temporary data and sortwork is done there.
3) Make sure tempdb is set to truncate log on checkpoint, so the log segment won't fill either. Also, make the log about 25% of database size and put it on separate logical device.

I'll bet  that one of these will solve your problem.
0
 
bamartinoCommented:
Did you try to use begin transaction and commit transaction without expecting everything to be uncommited up to the end of the process?
0
 
Victor SpiridonovCommented:
What is the size of your tempdb? The initial default size of 2Mb is not too much, since all sorting and grouping operations are performed in temdb. To be on the safe side I would make it initially 50-100Mb.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Mak042298Author Commented:
1st. bamartino. I'm not using BEGIN TRANS - COMMIN TRANS
2nd.spiridonov. The size of tempdb is 30MB. But the strange is the INSERTS are filling up the tempdb, not the SELECTS or SPs.
0
 
bamartinoCommented:
Either increase the size of tempdb or then use begin tran commit tran. Your tempdb gets full when you are not commiting data and holding everything to the end.  Working tables are created until you finish your transaction if these working tables get out of hand in size then your tempdb explodes.
0
 
Mak042298Author Commented:
I tried, but BEGIN - COMMIT doesn't seem to work, not even with small chunks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.