SQL - Delete table with 372 million rows starting from first row

Question

I have a table with 372 million rows, I want to delete old rows starting from the first ones without blocking the DB. How can I reach that?

The table have

id | memberid | type |      timeStamp          | message |
1     123        10    2014-03-26 13:17:02.000    text

UPDATE:

I deleted about 30 GB of Space in DB, but my DISC is ON 6gb space yet.. Any suggestion to get that free space?

Thank you in advance!

Answer 1

select 1;
while(@@ROWCOUNT > 0)
begin
   WAITFOR DELAY '00:00:10';
   delete top(10) from tab  where <your-condition>;

end

delete in chunks using above sql

Answer 2

You may want to consider another approach:

1. Create a table based on the existing one
2. Adjust the identity column in the empty table to start from the latest value from the old table (if there is any)
3. Swap the two tables using sp_rename
4. Copy the records in batches into the new table from the old table
5. You can do whatever you want with the old table.

BACKUP your database before you start deleting records / play with tables.