Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 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?

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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