Follow these rules to use minimally logged mode DML in an explicit begin transaction, followed by multiple batches of DML statements.
You may use DML statements that operate on different tables that are either in fully logged or minimally logged mode in a multi-statement transaction. You can use the set dml_logging command to change the logging mode partway through a transaction, but its effect on subsequent DML statements varies, depending on what other DML operations have already been executed in the same transaction. The following SQL transaction is allowed, assuming that the logging mode is full when you start the transaction:
begin tran go delete t1 where ... go set dml_logging minimal go insert t1 values ... go update t2 where ... go commit tran go
delete t1 is performed
with full logging, but update t2 is
performed in minimally logged mode. In the case of a rollback, the
update is not undone, but the delete is rolled back.
Once you execute a command with full or minimal logging on a table in a multi-statement transaction, subsequent commands on the same table must use the same logging (full or minimal), regardless of the session’s dml_logging setting. In example above, the insert t1 is executed with full logging because the earlier delete on t1 which was executed with full logging.
Conversely, resetting the session’s logging mode to the default mode may not resume logging for a DML statement if the table was previously operated on in a minimally logged mode in the same transaction.
Mixing the logging mode for different tables within the same transaction has different results for the tables involved if the transaction is rolled back. Changes to tables with full logging are rolled back, whereas changes to tables with minimal logging remain committed.
The rules for logging mode choice and use of set dml_logging command inside stored procedures are identical to those for a multi-statement batch. If:
The procedure is run outside an explicit transaction, then each statement is executed as an individual transaction.
The procedure is run inside a transaction, then the same rules described above apply.
There is no restriction on changing the logging mode inside a transaction and then executing select from a table that was previously operated on in the same transaction in a different logging mode. The delete t1 is performed in logged mode, while the update t2 is performed in minimally logged mode. Referencing the same table t1, for read, which was once operated on in fully logged mode when the logging mode is now minimal is not an error.
begin tran go delete t1 where ... go set dml_logging minimal go update t2 where t2.c2 = (select c1 from t1 where ...) go commit tran go
