Hi Friends,

Small clarification needed on Recovery Models.

As per my knowledge, If we choose Simple recovery model, we cannot take Transaction Log backup. Right! and the reason for this whenever 70% of the Log is full , Sql server issues a CKPT and committed txns are written back to .mdf file and that space if freed up and it can be overwritten other txns in the .ldf file. And so for this reason, we dont often see any increase in the Transaction Log. And so for that very reason, i.e. since it is getting overwritten, there is no use of taking the T-Log backup. This is again as per my understanding. pl correct me if am wrong!!!

Coming to Bulk logged recovery model, only minimal things are logged
inside my Transaction - log i.e. not every operation is logged.
and if we do bulk insert operation say (10 million records) am loading, then it wont log all my 10 million operations in T-log. Right! Then my question is , why did Microsoft has provided a T-Log backup in the case of Bulk Logged Recovery Model.

Also, if anything failure occurs in the middle of my BCP operation
entire thing is ROLLBACK'd. Then what is the point in taking the T-log backup as in case of Bulk logged Recovery Model. How it is going to help me out during my recovery phase?

can Anyone help me out in understanding??

Any practical scenario will be more helpful!!!

Thank You!

bulk operations is also minimally logged in Simple recovery model
i.e. for bulk operations only the allocation of extents is logged
Let's assume your bcp commits in batches of 30000 records
And lets assume that while inserting the second batch your server unexpectedly reboots.
Now the recovery phase will know which allocated pages was committed and which once not. So you will see your committed data.
That is why: