Solved

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

Posted on 2010-09-09
7
1,553 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

617 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