Solved

tempdb full with INSERTS?

Posted on 1998-10-21
6
300 Views
Last Modified: 2008-02-26
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
Comment
Question by:Mak042298
6 Comments
 

Expert Comment

by:bamartino
ID: 1090722
Did you try to use begin transaction and commit transaction without expecting everything to be uncommited up to the end of the process?
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 1090723
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
 

Author Comment

by:Mak042298
ID: 1090724
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 2

Accepted Solution

by:
formula earned 100 total points
ID: 1090725
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
 

Expert Comment

by:bamartino
ID: 1090726
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
 

Author Comment

by:Mak042298
ID: 1090727
I tried, but BEGIN - COMMIT doesn't seem to work, not even with small chunks.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question