3

If my recovery model is BULK LOGGED, I'm in a transaction, and I've issued some statements that are minimally logged like SELECT * INTO, will a ROLLBACK undo those minimally logged statements?

1

I decided asking here was just being ultra-lazy so tested it myself.

create table tmp_insert_in_txn ( something int not null primary key ) 
begin transaction 
    select 1 as something into tmp_created_in_txn
    -- show the value was inserted
    select something as in_txn from tmp_created_in_txn

    insert into tmp_insert_in_txn  with ( tablock )
    (   something   )
    select something from tmp_created_in_txn    

    -- show the value was inserted
    select something as in_txn from tmp_insert_in_txn
rollback
-- no rows after rollback:
select * from tmp_insert_in_txn
-- no table after rollback:
select * from tmp_created_in_txn

This shows that the rollback does remove the table tmp_test and the row from tmp_test2, so the answer is that minimally logged operations are still rolled back.

Hmm... so how come using them prevents the transaction log from getting really big?

  • Welcome to any SE site, where Jeapordy Q are just fine. However, there's some rumbling that you've raised more questions with this. I've asked those doing the rumbling to kindly ask you those Q. We shall see. – jcolebrand Mar 13 '12 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.