Delete data from table in chunks without getting out of disk space due to transaction log
Today I found myself trying to delete million of rows from a table with over 200.000.000 rows. A simple Delete From tbData Where ID < 100000000 resulted in a full disk space and a huge transaction log file. The solution came from this post:
deleteMore:
Delete TOP(10000) tbData Where CDRID < 100000000
IF @@ROWCOUNT != 0
goto deleteMore;
The TOP(10000) is up to you and you should adjust it to your data. For me the optimal value was 500000.
deleteMore:
Delete TOP(10000) tbData Where CDRID < 100000000
IF @@ROWCOUNT != 0
goto deleteMore;
The TOP(10000) is up to you and you should adjust it to your data. For me the optimal value was 500000.
Comments
Post a Comment