472,325 Members | 1,583 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,325 software developers and data experts.

Problem with Delete

I am facing a problem with DELETE statement. I have to delete around 2 billion records from a table with 150 columns. on execution of the delete statement, it fails after some time with message " Log Space full".

I am having a log space og 2 GB. But I guess thats proving in sufficient. I am using one indexed column to delete the data. If I use other column as filter statement I have to run the query more than 1000 times.

Is there any way to overcome this?
Dec 11 '07 #1
2 1331
297 Expert 100+

if you want to delete all rows from that table, a "LOAD (client) from empty.file of del replace into my.table" is the fastest way to do.

If the number of rows to be deleted is far more that those to be preserved, build a temp. table, declare a cursor on your original table and SELECT the rows you want to keep. Then LOAD these from the cursor into the temp. table, and afterwards reverse the LOAD (with replace option).

If you have to stick to a DELETE statement, there's no other way than to increase the log space and/or do it in several steps, e.g. if you got a date column, set a limit on monthly base. You could also choose to aprox. cut in half the amount of rows to be deleted, time and time again, as long as it takes to run the DELETE successfully. That way you'll learn how many rows/data of that table you may delete with 2GB of log space.

Regards, Bernd
Dec 16 '07 #2

If you are a DBA then you just update CFG parameter logsecond -1 then executive the command after completion of that u can reset this CFG
Dec 19 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

by: michael | last post by:
Gotta post because this is driving me nuts. Trying to DELETE orphans. I can successfully: SELECT GroupID FROM Groups LEFT JOIN Users ON...
by: Matan Nassau | last post by:
Hello. i have a composite which i want to delete. this is a composite which represents a boolean expression (see a previous post of mine with...
by: Bob Darlington | last post by:
I have a repair and backup database routine which runs when a user closes down my application. It works fine in my development machine, but breaks...
by: placid | last post by:
Hi all, i have this struct for a binary tree node typedef struct treenode { char *word; struct treenode *right; struct treenode *left;...
by: George | last post by:
VB.net 2003 standard, XP windows home edition. Installed first application OK today. When I removed the application via Control Panel, there were...
by: Lighter | last post by:
Big Problem! How to overload operator delete? According to C++ standard, "A deallocation function can have more than one parameter."(see...
by: Rebecca Tsukalas | last post by:
Hello, I have a problem concerning removeChild. This is the XML structure I use with php: <xml_thing <language1 <site>bla1</site...
by: Martin | last post by:
Hi all, This is the situation... DatagridView Control with datasource set to datatable. I want the user to be able to delete x number of...
by: dean.cochrane | last post by:
I have inherited a large application. I have a table which contains a hierarchy, like this CREATE TABLE sample_table( sample_id int NOT NULL...
by: Ed Dror | last post by:
Hi there, I'm using ASP.NET 2.0 and SQL Server 2005 with VS 2005 Pro. I have a Price page (my website require login) with GridView with the...
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.