Delete Large amount of Records

RosOmar

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.

Juhl

Truncare the table in sql

jordi79

Truncate should work...
Just ensure that database is on single user for it to be faster.

bbrown

Running that "Object" code would be a VERY BAD idea.

DenSter

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.

Juhl

Truncare the table in sql

jordi79

Truncate should work...
Just ensure that database is on single user for it to be faster.

RosOmar

Gents,

Thank you very much! Truncate Table from Server Management Studio worked like a charm. All data GONE within a second.

kriki

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.

bbrown

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.

RockWithNAV

I also go on SQL for these requirements.