>>>Hi all,
>>>Every 2-3 weeks I load 0.5-1.5 million rec into server table and delete "expired" records. I delete records from server table using BCP+Fox solution (DROP INDEX for all of them, BCP the whole table (3-5 mln rec) out, APPEND FROM tblOld.txt FOR myConditions to Fox table, COPY TO tblNew.txt, TRUNCATE server table, BCP in, CREATE INDEX ).
>>>It works good enough and using BCP+Fox I avoid transaction log nightmare that kills all SQL Server solutions I tried, but I am still interested in "Server only" solution and wondering how others deal with such problem.
>>>TIA and Happy New Year!
>>>Oleg
>>
>>I like drop table for that volume.
>>
>>So I would copyt good to a temp table, drop the table, recreate it and bring the good data back. Recreate the indexes.
>Stephen and Andrew,
>SELECT "good data" into temp (3 mil rec) and back (or DROP+RENAME instead of bringing back) will have pretty much same problems as DELETE (it is "logged" operation).
>Mark Allen suggested separate table for each mail drop and in this case SELECT INTO will copy only 10,000 rec. OTOH using several tables instead of 1 will require changes in applications.
>I was hoping that there is some magic trick to turn off transaction log for mass DELETE or SELECT INTO.
>Thanks,
>Oleg

I didn't understand that there was 3+ million kept as good, on a time limited basis.

I don't know how to but I'm sure that you could set that one table to be separate in loging. I can't remember how to at this time. I'm more rooted in SQL Server 4.5, and 6.0 days.

__Stephen