|
I am trying to delete 80,000+ records from a table in a database I am testing now but want to perform the actions against a live customer database This the the script I am using:
begin set @itemsDeleted = 0; for itemsToDelete as c_itemsToDelete cursor for select inv_pk as invPK, inv_cpk as invCPK from stockinventory where inv_brd_fk = 1474 and inv_brd_cfk = 1000 do begin delete from stockinventory where inv_pk = invPK and inv_cpk = invCPK; commit; set @itemsDeleted = @itemsDeleted + 1; exception when others then rollback; end; end for; message trim(str(@itemsDeleted)) + ' Items Successfully Deleted'; end After about 20 minutes of run time and over 30,000 deletes the database will assert with the following Attempting to normalize a non-continued row (0x9315:0xd) ERROR Assertion failed: 200610 (10.0.1.4239) I can restart the db and restart the script and the script will run to completion Why is this assertion happening? What can I do to avoid it? |
|
There are a couple of possibilities (from our database of past issues) and database corruption is the likely cause. Corruption could be from media failure, or potentially from a flaw in the server that has since been fixed. Does VALIDATE TABLE report any errors? |
Just a sidenote: Is there a need to do a COMMIT after each delete - or could you group several statements into on single transaction?
A different (and easier) approach to delete "in chunks" might be to use "DELETE TOP n FROM ... ORDER BY ..." - that way you would not need a cursor loop.
Sorry, it this is outdated. Years ago we had a counting problem in UNIX, it would run out of process id's at about 30000 or 32,000.... Maybe it is a counting over flow at 32768. Sorry if I just dated myself. I would find a different method, it may be out of your hands.