mass delete - built for speed - mssql tsql

[ecka00]

what is the quickest way to purge the first 15 million rows in a 42 million row table? FYI---there is a row_no and also a date_added column in the table. i could key off a date or the row_no in the query--there is a direct coralation between the first 15 million rows and said columns. -allen

[rmiao]

By multiple small batches based on those columns.

[ecka00]

should i use a cursor and do 1 row at a time?

[rmiao]

Or try with loop.

[skhanal]

remember to put the database in simple recovery mode, this will prevent the log file running out of space.

[ecka00]

yep, that was one of the first steps i took. although i didn't restart sql or the agent after said change--assumimg change was dynamic?

[DanB]

copy the current table structure to a new table without indexes. select the records you want to keep into the new table. drop the old table.

[rmiao]

Don't need restart sql after changing recovery mode, change it back once done with deleting rows.