Wednesday, May 25, 2011

BULK_LOGGED Recovery Model

This model is to be used only while performing bulk operations only as we cannot perform point in time recovery in this model.

I always learnt it minimally logs bulk logged operations and only learnt this point. So finally in one interview a smart guy asked what it logs I said “it logs the bulk operation minimally and fully logs other transactions”. He asked minimally means what it will log actually. I don’t have any answer other than a smile.

Later went back to home and started asking the only guru available always ‘google’ and found below details:

A database set to this model will log only the details of which extents got affected due to bulk operation but the contents of the pages are not logged and hence the log file will not grow huge. So when performing backup only the affected extents due to bulk operation will be copied to the log backup.

So we are sure now if any log file backup contains bulk logged operations we cannot perform point in time recovery. How sql server knows what extents to copy to log backup is, it keeps a bitmap to all the extents that have been modified that is a bit is set to 1 on every extent that is changed due to bulk operation and these are copied.

No comments:

Post a Comment