Geek asks and answers

Deleting a record of 30-40 million without affecting performance

I have a SQL Server database running in full recovery mode. I need to delete data (about 30-40 million records), but I can not use the database offline, because it is used constantly. I also cannot switch to simple recovery mode if something happens and we lose data in real time. When I try to delete data in small chunks (about 2 million rows), the transaction log becomes extremely large and causes a very slow process. Due to the backup jobs working at night, I only have a small timeframe.

Does anyone have any thoughts on how I can do this? I thought about copying the table to another database (in simple recovery mode) and then deleting the data. Is that a good idea?

There are 3 tables. Campaign, events and goals. Its an event table that has millions of records, and that is what takes time to delete. All of them have the necessary relationships through the Id columns.

+3
source share
2 answers

You must use small chunks, otherwise your transaction log will increase

30-40 . "", 50 . vs : delete/insert

, , . , 30-40 - , , .

40 x 1 CHECKPOINT,

Bulk DELETE SQL Server 2008 ( - Bulk Copy (bcp) ?)

- :

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
BEGIN
    CHECKPOINT
    --Edit: must be last to set @@ROWCOUNT
    DELETE TOP (1000000) MyTable WHERE ...
END

:

  • ( , )

, 30 ...

+3

30-40 ? - (, " 10 " ), SQL Server. (. ) , .

pjjH

http://msdn.microsoft.com/en-us/library/ms191160%28v=sql.100%29.aspx

+1

More articles:


All Articles