Wednesday, February 15, 2017

sql server - How to delete large data of table from SQL without lagging? - MS Dynamics Nav

Deleting large chunks of data from querry in SQL?



Requirement :- While deleting G/L Entry and Item ledger entry for creating an activity in Upgradation I came across this requirement.This post is just containing the best SQL Querry which I managed to find out and Test to my Level.

I had 120 millions of Lines in GL entry which was almost impossible to delete using C/AL Language.

Thanks to Stack Over Flow in finding me this querry.
Database Reference
Stackoverflow Link











Please follow these Links for more detailed querry in Sql:-

Link 1

Link 2

Link 3

Link 4

The Best querry which I can find is :-

WHILE (1=1)
BEGIN
DELETE TOP(1000) FROM [Yamaha Music India Private Ltd$Change Log Entry]
IF @@ROWCOUNT < 1 BREAK
END

You can adjust the TOP value according to your need and speed. This is a tested querry you should put it in Querry page of SQL. and Please use with high priority as it will delete all data from table.

Best of Luck
Best Of  Luck