I have a table in a database on SQL 2000 Std. that has grown to over
314 million rows.  I need to delete the vast majority of these, but as
I expected Query Analyzer runs perpetually and I'm forced to cancel
after several hours. The frustrating piece is that I cannot seem to
even delete 1 row!  In Enterprise Manager I can list the top 100 rows,
and even when I manually select a row and click "Delete", Enterprise
Manager will run until it times out.

Has anyone had to clean up a large database such as this?  The
database was recently indexed and compacted, but still huge.

TIA

A couple things you can try:

1). Use SET ROWCOUNT to split the delete, like this:

SET ROWCOUNT 10000
WHILE 1=1
BEGIN
  DELETE FROM MyTable
  WHERE <condition>

  IF @@rowcount < 10000 BREAK
END
SET ROWCOUNT 0

2). As you indicate the majority of the rows have to be deleted, then it
might be easier to select the rows to keep into another temp table and then
truncate or re-create the original table to insert the data back (or just
drop it and rename the temp table to the original table name)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Hello,

Do the steps after taking a db backup.

Please do not delete the data using a single DELETE command, instead use a
key column which determins
the delete condition and perform the deletion in iteration. Sample is:-


   SET ROWCOUNT 50000
   WHILE 1 = 1
   BEGIN
      DELETE table_name WHERE ID Between --mention range
      IF @@rowcount < 50000
         BREAK
   END
   SET ROWCOUNT 0

Do this iteration till the data you required is deleted.

If you consider the below points deletion can be faster:-


1. If trigger is available on the table disable it using ALTER TABLE and
DISABLE TRIGGER.
   and later enable the trigger

2. If you have Foreign key then either disable the key or make sure that FK
column has an index in child table

Thanks
Hari

<xut***@gmail.com> wrote in message
Show quoteHide quote

In addition to the other posts, deleting a row though EM isn't a good display what how fast you can
delete a row. Use a DELETE statement instead. And, remember that SQL Server need to find the row in
order to delete it, so indexes is as crucial for deletes as for other DML statements.

Show quoteHide quote

Changing the text of the code of multiple stored procedure
SQL Server Backup and Transactions
Deferred Name Resolution gone wild.
How to avoid creating duplicate indexes on the same column
SQL Server 2005 compact edition with VB
Table size
SA Password help
Restore Failure OS Related ?
ISNULL not working
Conversion failed when converting datetime from character string