Auteur de questions
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.
Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
- Proposé comme réponse Harsh ChawlaMicrosoft employee mardi 21 février 2012 05:36
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
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