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

1

Yes.

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?

2
  • 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, 2012 at 14:45
  • 1
    When a minimally logged transaction happens, that transaction is the only one that is writing to the pages in question. So in your example the minimally logged transaction is the only one writing to the pages that are being allocated to tmp_created_in_txn. If another tran needed to write it would need a new page. When the rollback happens, the log simply tracks page numbers, and all those pages are reset to blank.
    – mrdenny
    Jun 28, 2021 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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