Solved

tempdb full with INSERTS?

Posted on 1998-10-21
6
298 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SPROC to look for existing record in passed table name 7 46
SQL Server Deadlocks 12 47
Add '#' to end of file 2 29
Help Extract Specific in SQL 8 15
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

932 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now