Massive data deleting problem | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Massive data deleting problem

There is a massive data deleting job I need to do in my company, there is huge table named report in our main sql2000 database server, the total records number is more than 27,000,000, we need to delete 100,000 records which matches specific query criteria, when I run the delete statement, I’ll run a very long time, and CPU utility of the database server always reach at a peak level. Can anyone share me a good method I can quickly delete those data? Best Regards Denzel
I presume you are using a delete from statement.
What is the criteria for the deletion?
Are you using the correct index? Can you share some of the code and possibly some of the table design?
Post table structures and the query you used Madhivanan Failing to plan is Planning to fail
As Madhivanan and krammer said it is difficult to give an suggestion without looking at the query. Can you try the same query after re indexing the table indexes —————————————-
Cast your vote

In this moment the only concern is Transaction log size during this query execution, ensure to maintain the smaller transaction to delete the data and to perform frequent log backups during this process. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.