When I execute the following SQL statement:
DELETE FROM test_table where id between 1 and 500000;
I get the error log:
The transaction log for the database is full.
How-To
This error resulted from "All space in the transaction log is being used."
I need to modify the delete statement into 5 SQL statement, delete 100000 records in each statement instead of delete 500000 at one time.
The updated delete SQL statement are the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | -- delete rownumber from 1 to 100000 DELETE FROM test_table WHERE id IN ( SELECT id FROM (SELECT id, row_num() over(ORDER BY id) AS rownumber FROM test_table) WHERE rownumber BETWEEN 1 AND 100000 ); -- delete rownumber from 100001 to 200000 DELETE FROM test_table WHERE id IN ( SELECT id FROM (SELECT id, row_num() over(ORDER BY id) AS rownumber FROM test_table) WHERE rownumber BETWEEN 100001 AND 200000 ); -- delete rownumber from 200001 to 300000 DELETE FROM test_table WHERE id IN ( SELECT id FROM (SELECT id, row_num() over(ORDER BY id) AS rownumber FROM test_table) WHERE rownumber BETWEEN 200001 AND 300000 ); -- delete rownumber from 300001 to 400000 DELETE FROM test_table WHERE id IN ( SELECT id FROM (SELECT id, row_num() over(ORDER BY id) AS rownumber FROM test_table) WHERE rownumber BETWEEN 300001 AND 400000 ); -- delete rownumber from 400001 to 500000 DELETE FROM test_table WHERE id IN ( SELECT id FROM (SELECT id, row_num() over(ORDER BY id) AS rownumber FROM test_table) WHERE rownumber BETWEEN 400001 AND 500000 ); |