Delete Large amount of Records

RosOmarRosOmar Member Posts: 7
Ladies and Gents,

Due to some wrong functional setup and programming, one of our clients now has a table with 2bln lines which takes upto 2tb of storage. The table data is not being used anywhere anymore.....;)

I have been trying to delete these through processing reports and codeunits, with deleteall. This does not work (ie takes too long and/or sql error).

Online I have seen someone mention following:

"Object.SETRANGE(Type,Object.Type::TableData);
Object.SETRANGE("Company Name",COMPANYNAME);
Object.SETRANGE(ID,DATABASE::"Customer Price Group"); // This is the table to flush
Object.DELETEALL;
"

Would appreciate your opinion or a proposed solution.

Thanks in advance.

Best Answers

  • JuhlJuhl Member Posts: 724
    Answer ✓
    Truncare the table in sql
    Follow me on my blog juhl.blog
  • jordi79jordi79 Member Posts: 273
    edited 2021-11-24 Answer ✓
    Truncate should work...
    Just ensure that database is on single user for it to be faster.

Answers

  • bbrownbbrown Member Posts: 3,268
    Running that "Object" code would be a VERY BAD idea.
    There are no bugs - only undocumented features.
  • DenSterDenSter Member Posts: 8,305
    Find a way to split it up into smaller chunks and do a COMMIT in between chunks. Maybe there's a field with a foreign key that you can filter on. For instance, if I were to delete 2 bln Item Ledger Entries, I would probably run it per Item, per Location, and per month on the posting date.
  • JuhlJuhl Member Posts: 724
    Answer ✓
    Truncare the table in sql
    Follow me on my blog juhl.blog
  • jordi79jordi79 Member Posts: 273
    edited 2021-11-24 Answer ✓
    Truncate should work...
    Just ensure that database is on single user for it to be faster.

  • RosOmarRosOmar Member Posts: 7
    Gents,

    Thank you very much! Truncate Table from Server Management Studio worked like a charm. All data GONE within a second.
  • krikikriki Member, Moderator Posts: 9,110
    For a truncate to be faster, you don't need the database in single user. truncate is logged in the transaction log in SQL Server, but unlike deleteall which logs ALL single records in the transaction log, truncate logs only the truncate command. Truncate on a table has also as side-effect, that it resets the autoincrement on the field in that table (if there is an autoincrement field on that table). truncate on a table should only take a few seconds.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • bbrownbbrown Member Posts: 3,268
    Truncate doesn't actually delete anything. It simply reallocates the space in the database. That is why it is so quick. It's similar to "deleting" a file from Windows. All that happens is to reallocate the space and remove the directory entry. That's why "Recovery Bin" works.
    There are no bugs - only undocumented features.
  • RockWithNAVRockWithNAV Member Posts: 1,139
    I also go on SQL for these requirements.
Sign In or Register to comment.