none
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
    lundi 6 février 2012 11:13

Toutes les réponses

  • 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
    lundi 6 février 2012 11:30
  • 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_villhttp://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
    lundi 6 février 2012 11:58
  • 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

    mardi 7 février 2012 13:52