Solved

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

Posted on 2010-09-09
7
1,550 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:chinawal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33636967
it will only avoid that error if you put a WAITFOR DELAY into the loop, and ensure you have transaction log backups running during that "time" ...otherwise, you could also "delete at once ..."
0
 
LVL 11

Expert Comment

by:Larissa T
ID: 33637136
If you don't need maintain txn log for this DB
you can add this after update to avoid tempdb full error.
backup log databaseName with truncate_only


0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 33637283
trofimoval:
 WITH TRUNCATE_ONLY command is discontinued from SQL Server 2008.
you cannot suggest that, the question posted in sql 2008, and it should not be suggested anyhow, under normal conditions ...
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 11

Assisted Solution

by:Larissa T
Larissa T earned 100 total points
ID: 33637414
angelIII
 I didn't know that it was deprecated, Thank you for information.
BTW,  I would appreciate if you look on this open question when you have time
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26433435.html
0
 
LVL 13

Assisted Solution

by:dwkor
dwkor earned 100 total points
ID: 33638749
I believe you have clustered index on key column?

I would suggest to put delete statement in the transaction:

      WHILE @rows > 0
      begin
             
             begin tran
                DELETE TOP (100000) FROM HugeTable
                Where Key < 29660822
                select @rows = @@rowcount  
             commit            
      end

If you have FULL recovery mode, you can perform transaction log backup after each transaction like AngelIII suggested. I would do the separate transactions even if you don't backup the log in order to minimize locking.

If you need to perform this action regularly, you could think about table partitioning.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 33640675
The easiest way would be to put the db in SIMPLE recovery mode [... you almost certainly can't do that since it's a prod db, but that is the easiest way :-) ] .

In FULL mode, you might try reducing to 50K at a time.  However, as noted earlier, the only way to clear that log space is to back up the log.

[Btw, you don't need explicit transactions here, each statement will implicitly be its own transaction.]

If the WHERE value is the first/only clus key column, verify that SQL is using  the index to do the DELETEs and, if it's not, "force" it to with a "hint" ("INDEX (...)").


How many indexes are on the table?  You also need to consider that when sizing the DELETEs, since very DELETE will naturally have to be applied to all indexes on the table as well.
0
 
LVL 1

Assisted Solution

by:jwarero
jwarero earned 100 total points
ID: 33652239
Hi. Is this a one time task or a recurring one? does the table have any relationships setup . What ad do is the following
1) prepare a create table statement script based on the structure for HugeTable
2) rename the table HugeTable to HugeTable_1 from SSMS
3) run the create table statement to recreate the HugeTable (which would now contain no records)
4) do an insert statement to retrieve only the records that you would like to retain from HugeTable_1
5) Remove any relationships inherited by HugeTable_1
6) Drop table HugeTable_1

This all depends also on whether you have referential integrity relationships set up with other tables which would require that you drop the relationships then recreate them. I believe this would take a shorter time than running a delete on the table

jwarero
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question