Deleting bulk records from SQL database

Question

Dear Experts,

We have a requirement to delete approximately 5 million records from one SQL 2008 database. May you please guide me with some best pratices to follow for the same without creating new issues like log space.

Thanks!

---

Thanks

Answer 1

Have a look at this article  http://blogs.msdn.com/b/sqlcat/archive/2009/05/21/fast-ordered-delete.aspx

You have alternatives in the comments section.

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com

Answer 2

Hello,

 

You should delete the rows in batches. That will help you to handle the transaction log usage without filling it up.

 

Below example deletes from My_Table using batches of 50000 rows. 

 

You should update the statistics when you are done deleting

 

USE My_Database

GO

 

Set RowCount 50000

Declare @UpdatedRow int

 

Set @UpdatedRow = 50000

While @UpdatedRow = 50000

Begin

      DELETE FROM MY_Database.dbo.MY_Table WHERE ID >= 256842

     

    SET @UpdatedRow = @@RowCount

End

 

GO

Set RowCount 0

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 3

Hi,

If the 5 million records are in full one table, then you can go for truncate, which will use less TLog file,

for backup you have to make sure that you save the last full backup

Jai

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

---

Jai Shiva Naidu Verizon