troubleshooting Question

Delete 29 million rows from 1 billion rows table. (SQL Server 2008)

Avatar of chinawal
chinawalFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
7 Comments5 Solutions1686 ViewsLast Modified:
I need suggestions from experts.
I need to delete 29 million rows from 1 billion rows table.  This is SQL Server 2008.
I am sure foillowing query will work. I know that it will take an couple of hours or more to run it.
Does anybody have any suggestions/improvments in following queries? My main aim is to have these queries consume minimum resources and avoid 'transaction log full' error.


      Declare @rows int
      set @rows= 1

      WHILE @rows > 0
      begin
             DELETE TOP (100000) FROM HugeTable
             Where Key < 29660822
                   
             set @rows = @@ROWCOUNT

             CHECKPOINT
      end
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 5 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 5 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros