dml without generating log transactions ?

Hi There

I know the answer to this is probably no, but had to ask anyway.

Is there a way to perform a dml statement without generating anything in the transaction log ?

The reason i ask is that i have a database that uses simply recovery model, however i need to move a 1 billion row table to this DB, i know that even though it is in simple recovery it is one transaction, it will be written to the log until committed then the space will be released in the log file.

I am using a simple: insert into DW_DB..table select * from DB..other_table.

I have dropped all indexes before the operation.

However this is a big problem, the log for the db in simple recovery that i am moving the data to grew to 128 Gig and the disk ran out of space, the other drives on the machine do not have much space.

Is there a way i can move the billion row table into the new DB without generating such a huge log ?

Thanx

Hi There

Part 2 for the question:

The transaction has rolled back, however there is still 27 gigs space used in the transaction log, there are no open transactions in the db, the db is in simple recovery, i cannot backup the log as it is simple recovery, what is this 27 gigs in the transaction and how do i clear it ?

Thanx

|||

Please ignore my second comment, this problem went away after checkpointing the database, however any feedback ont he original post would be greatly appreciated.

Thanx

|||You can use select into command which is bulk operation and it is minimally logged in the case of simple recovery model.|||Thank you , this worked perfectly.