[anitaarbabi]

Hi,
I'm trying to trim down a huge table in sybase database. We have around 25 million rows in the table from 1997. We want to keep the most recent three years and delete the rest. The thing is we cannot shut down the database and it should remain functional during the process.

I tried doing a DELETE FROM TABLE WHERE DATE < 'date'. I had to cancel the query due to it taking too long and nearly taking down the server.

pleeeease heeeelp.

Thanks in advance.
Anita

[iinfi]

you will need to run it in parts.
like delete records frm 1997 to 1998 tonite.
further 1998-99 tomorrow nite.
likewise

[mike_bike_kite]

Would this do it for you. I imagine it would take a day to finish but you could alter the 20000 value to suit your needs. At least this way you wouldn't need to baby sit the process. It'll just delete 20k records then sleep for a minute until finished. I don't think it would hold any locks.

declare @cnt int
set rowcount 20000

select @cnt = 0

while @@rowcount > 0
begin
       select "Done", @cnt
       select @cnt = @cnt + 20000

       wait for delay '00:01'

       delete TABLE
       where DATE < 'your cut off date'
end

set rowcount 0

Mike

[corral]

Assuming a brief period with the table unavailable, you may consider this. It's a variation of the usual way of bcp'ing out the data to be saved, truncate the table, drop indexes, bcp in only the good data and recreate the indexes.
- Build a similar table, loaded with the data you wish to keep
- Ban access to the table
- Use sp_rename to swap table names. Now, the good table is the one you built with fewer data
- Copy the latest inserts, those arrived after you built the table
- Grant access again

A warning about sp_rename: views, stored procedures and triggers will still refer to the old table, so they need to be droped and created if contain references to the table.

Regards,
Mariano Corral

Originally Posted by anitaarbabi

Hi,
I'm trying to trim down a huge table in sybase database. We have around 25 million rows in the table from 1997. We want to keep the most recent three years and delete the rest. The thing is we cannot shut down the database and it should remain functional during the process.

[anitaarbabi]

Hi Mike,

I don't get what your code does. Could you please explain a little more?

Anita

[aflorin27]

The key statement in his code is "set rowcount":

set rowcount:
causes Adaptive Server to stop processing the query (select, insert, update, or delete) after the specified number of rows are affected. The number can be a numeric literal with no decimal point or a local variable of type integer. To turn this option off, use:
set rowcount 0

The code above deletes all your useless records, not in a single run, but in "batches" of 20.000 records.
I have to say it is a smart choice

[mike_bike_kite]

I don't get what your code does. Could you please explain a little more?

As explained above, set rowcount x just limits the number of rows affected by any sql. The little program just loops round and round deleting any data before your cut off date but limiting itself to only deleting 20k rows at a time. This means there will still be data before this date in the table when it loop round again. It will then wait for a minute to allow any other programs to do their stuff. It also shows a rough rowcount to show the progress. It will stop when there aren't any records left to delete before your cut off date.

Mike

PS1 You can change the 20k value and the wait value to make things faster or slower.
PS2 You should dump the database before doing any major change like this.