Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1

    Unanswered: Removing records in tables?

    hi colleagues,

    I need to delete records in table 20 gb, when i execute delete from schema.table the system show me this message the space log full. Therea is any way to remove records in the table for range or other way without use the logs?

    Thank you for you help.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Do you want to delete all of the rows in the table or just some of them?

    Andy

  3. #3
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Thank you for the quick response,

    In my table i have three year of work, I need romove one year of work.

    Thank you.

  4. #4
    Join Date
    Dec 2009
    Posts
    62
    You may increase the configs for LOGS (LOGFILSIZ, LOGPRIMARY, LOGSECOND), or you may delete rows in smaller steps, smaller quantities.

    Remember if you change configs you'll need to restart the service to make them work. In some cases a deactivate/activate cicle should do.

    Another approach (which I actually use) is to create a task that runs from time to time and delete a certain quantity of rows.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Deleting lots of rows has several issues.

    1) It takes a long time
    2) It requires lots of log space
    3) It requires lots of locks

    What you need to do is to break it up into chunks small enough that the reasons above are not affected. You want the chunk to process within the amount of time for the lock timeout, otherwise you run the risk of lock timeouts. Start by deleting 1, then 10, then 100, etc. rows and see how long it takes each time. When you get to about half of the lock timeout value, then stop there and then delete the rest of the rows in chunks of this size.

    Andy

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by georgipa View Post
    Thank you for the quick response,

    In my table i have three year of work, I need romove one year of work.

    Thank you.
    Have you considered range partitioning?

    Another way is to load the rows that you want to keep into a temporary table, then load replace them back to the original table. As usual when it comes to load this requires some thought and caution. Make sure you understand the process and what the consequences might be if something goes wrong during load.
    --
    Lennart

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by lelle12 View Post
    Have you considered range partitioning?
    and/or MDC.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  8. #8
    Join Date
    Aug 2011
    Posts
    45
    Hi,
    You can take export dump of table which u do not want to delete and then truncate the original table then import the data back into the table from the dump taken. This wont write logs.

    Thnx
    Harsh V

  9. #9
    Join Date
    Apr 2012
    Posts
    4

    Smile

    An alternative is to disable logging/recovery ... but be careful... if something go wrong.... sometime you must recreate the table...

    example:

    Code:
    UPDATE COMMAND OPTIONS USING C OFF;  
    
    ALTER TABLE YOURTABLE ACTIVATE NOT LOGGED INITIALLY;
    DELETE FROM YOURTABLE WHERE .... ;
    COMMIT WORK;  
    
    UPDATE COMMAND OPTIONS USING C ON;
    if you have more table to delete add additional "ALTER... DELETE ... COMMIT " stmt

    good luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •