Solved

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

Posted on 2010-09-09
7
1,548 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

821 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