• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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.
0
Mak042298
Asked:
Mak042298
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
formulaCommented:
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:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now