Massive delete in DB

[Antar]

Hello,
I have a huge database (2 GB / month) and after a while it is becoming
non-operational (time-outs, etc.) So I have written an SQL sentence
(delete) that can reduce around 60% of the db size without compromising
the application data needs. The problem is that when I execute it, the
db does reduce its size 60%, but the transaction log increases at the
same rate. Can I execute the sentence in a "commit" or
"transaction" mode so to impede the SQL Server write in the log?

Thanks for the help!

Antonio

[Simon Hayes]

You can't stop MSSQL using the log, but you can break a single DELETE
into multiple batches (see example below). If you back up the
transaction log from time to time during the batch delete (or put the
database in single user mode first), this should keep the log size
under control. It would probably be a good idea to try it out on a test
system first, and also make a full backup before starting, just in case
something goes wrong.

Simon

set rowcount 50000 -- or whatever

delete from dbo.MyTable
where ...

while @@rowcount > 0
delete from dbo.MyTable
where ...

set rowcount 0

[Simon Hayes]

Sorry - a mistake in the comments above. "single user" should be
"simple recovery".

Simon

[John Bell]

Hi Simon

If possible it may be advisable to do both!

John
"Simon Hayes" <sq*@hayes.ch> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Sorry - a mistake in the comments above. "single user" should be
"simple recovery".

Simon

[Simon Hayes]

John,

Yes, absolutely right. Personally I would aim to do it in an overnight
job, with the users locked out of the database as you suggest.

Simon

[AK]

instead of deleting tons of rows, you could SELECT INTO <new table> the
rows you intend to keep, drop the old table, and rename the new one.
Could be much faster

[DH]

"AK" <AK************@hotmail.COM> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

instead of deleting tons of rows, you could SELECT INTO <new table> the
rows you intend to keep, drop the old table, and rename the new one.
Could be much faster

Or BCP the WANTED data out (you can create a view of the wanted records and
bcp from the view), drop indexes, truncate, load it back in and reindex.
BCP can be done as a mostly unlogged operation.

One way or another, you probably want to reindex to reorganize the table.

I recommend:
1. Testing with a small quantity of data in test tables and view with same
structure as real tables.
2. Good backups.
- or -
A strong, good-looking resume all printed and ready to roll.