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
declare @itemsDeleted integer;

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?

asked 01 May '12, 14:49

poohgirl's gravatar image

poohgirl
1111
accept rate: 0%

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.

(01 May '12, 16:16) Volker Barth

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.

(02 May '12, 12:28) George

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?

permanent link

answered 01 May '12, 15:42

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×32

question asked: 01 May '12, 14:49

question was seen: 2,246 times

last updated: 02 May '12, 12:28