Solved

tempdb full with INSERTS?

Posted on 1998-10-21
6
295 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 2

Accepted Solution

by:
formula earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I tried, but BEGIN - COMMIT doesn't seem to work, not even with small chunks.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 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

11 Experts available now in Live!

Get 1:1 Help Now