lundi 6 février 2012 11:13
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.
Toutes les réponses
lundi 6 février 2012 11:30
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.
Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
- Proposé comme réponse Harsh ChawlaMicrosoft Employee mardi 21 février 2012 05:36
lundi 6 février 2012 11:58
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
Set RowCount 50000
Declare @UpdatedRow int
Set @UpdatedRow = 50000
While @UpdatedRow = 50000
DELETE FROM MY_Database.dbo.MY_Table WHERE ID >= 256842
SET @UpdatedRow = @@RowCount
Set RowCount 0
mardi 7 février 2012 13:52
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
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