What do you mean by "Minimally Logged" in bulk recovery model?

              Today I ask this question from one of my colleague and he starts repeating his prepared speech saying all bulk operations are minimally logged ….. And when I ask him what is minimally logged means ….. There is a pause and he says it doesn’t write everything in log file and babble….

I asked him, does he actually know what does it write or what it isn’t??

He told me TechNet definition: “Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery”.

Does this answer my question? Noooo a Big NO

So here’s the missing link, Minimal logging means SQL server engine will only write Undo part of transaction and leave writing Redo part of transaction.

Ok that helps but how does this work in disaster

Case: Database is in bulk recovery model and I ran bulk transaction which takes 1 hour to write 10 GB data

Scenario 1: if SQL server crash after 30 minutes of transaction

è This will be simple and same as full recovery model. In case of crash SQL server will read t-log file and apply undo for all uncommitted transaction.

Scenario 2: If SQL server crash just after marking commit in t-log file whereas 2 pages are still dirty and waiting to get written in disk.

è Now here is the suspense. “My t-log file doesn’t have redo part of transaction and before those 2 pages would have written from ram to disk, we have lost them in crash”, But I never lose my data in this scenario

Because in minimally logged operation SQL engine guarantees that the modified data pages will be on disk at the time the transaction commits and hence the minimal login has no effect on the crash recovery process.

SQL internals Book says here: “For operations that were minimally logged in simple or bulk-logged recovery model, another process guarantees that the extents that were modified by the minimally logged operation are hardened on disk before the transaction is complete. This is known as an Eager Write, and is done by the thread that is executing the bulk operation. This is in contrast to normal operations where only the log records have to be hardened before the transaction is complete and the data pages are written later by a system process (lazy writer or checkpoint)”

              Minimally logged operations are usually faster than normal operations, but that is not guaranteed. The only thing that is guaranteed is that they write less information to the transaction log. So remember minimally logged operation can be slower than a regular transaction if only the data file is not able to handle the large volume of writes.

Tip: When transactional replication is enabled, BULK INSERT operations are fully logged even under the Bulk Logged recovery model.