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
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?
-
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 ♦Commented Mar 13, 2012 at 14:45
-
1When 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.– mrdennyCommented Jun 28, 2021 at 21:04