473,322 Members | 1,566 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Problem with Delete

Hello,
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 1387
docdiesel
297 Expert 100+
Hi,

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
Hello,

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

2
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 UsersID = UserID WHERE UsersID IS NULL; but when I...
6
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 more details at...
13
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 on a client's at the following line: If...
14
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; }TreeNode;
5
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 no problems and the app folders were deleted. ...
6
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 3.7.3.2); however, I don't know how to use an overloaded...
1
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 <site>bla2</site <site>bla3</site </language1
2
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 rows by selecting a row with mouse then clicking a...
2
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 parent_sample_id int NOT NULL ....lots of other...
11
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 following columns PriceID, Amount, Approved,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.