Transaction logs when nukeing a table

[Biggles]

I have a AX3.production environment very low on transaction log space, they
also have a table with 5 million records which needs completely emptying.

My suggestion was to backup the database, put it in simple mode, delete the
table records in batches of say 1000 so that it commits after the delete,
and hopefully this will not increase the size of the transaction log.

Am i correct in my theory? Whats is the best way to delete (commit) in
batches?

I would normally use "delete_from SysTraceTableSQL;" to just drop
everything, because that ignores all the validation (which is a problem we
have on that many records)

How would I do this with some sort of Counter so that after 1000 records
were delete it commit the transaction and then continue until the next 1000
were deleted etc. in as an efficient a statement as the delete_from so that
speed was not affected to much?

kind regards,

Biggles

[Gandhinath Swaminathan [MSFT]]

Biggles,
DELETE_FROM will not gaurantee set based operation. Especially when you
have security enabled or overwriten delete method, it will translate to row
by row operation. It becomes very slow and transaction heavy.

Is your requirement to delete the rows and not have any kind of log? If you
have back ups, I would recommend issuing a truncate table statement. Please
refer to MSDN for more information on truncate statement before you use it.
This statement does not log anything to the transaction log. However there
is no equivalent of truncate table in X++. You need to create a statement
class and execute it from X++.

--
This posting is provided "AS IS" with no warranties, and confers no rights
"Biggles" <axa...@xptech.co.uk> wrote in message
news:%23r9I9%239xIH...@TK2MSFTNGP03.phx.gbl...

[Vanya Kashperuk Ivan at dot nospam]

You can try truncating the table from the following form:

Administration\Periodic\SQL administration

Select the table you need to delete the data from and select "Table
actions\Truncate"
--
Kashperuk Ivan (Vanya), SDET, Inventory management, Microsoft Dynamics AX
My blog - http://kashperuk.blogspot.com
Download MorphX IT in Russian - http://www.lulu.com/content/723888