How to clear Change Log Entry table from SQL?

In my previous article about Change Log, I have explained how to clear Change Lot Entry table from Dynamics NAV. However, I also mentioned, that when clearing entries from Dynamics NAV, system is deleting only one change log entry at a time. To make the process faster and more resource-efficient, you can use this SQL query:

/*

Removes all rows from a table without logging the individual
row deletions. TRUNCATE TABLE is similar to the DELETE statement
with no WHERE clause; however, TRUNCATE TABLE is faster and uses
fewer system and transaction log resources.

TRUNCATE TABLE
    [ { database_name .[ schema_name ] . | schema_name . } ]
    table_name
[ ; ]

*/

TRUNCATE TABLE
  [Demo Database NAV (6-0)].[dbo].
  [CRONUS UK Ltd_$Change Log Entry]
;

Compared to the DELETE statement (and to Change Log Entries being delete from Dynamics NAV), TRUNCATE TABLE has the following advantages:

  • Less transaction log space is used.
  • Fewer locks are typically used.
  • Without exception, zero pages are left in the table.

Related Links

Technorati Tags: ,

This entry was posted in Dynamics NAV / Navision, SQL and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *