0

I am trying to understand the logging aspects in Full and Bulk recovery models.As per MSDN ,select * into test from abcsd is logged minimally in Bulk logged ,but in full recovery model,it is logged fully.But while reading transaction log i am seeing different behaviour,Can please some one shed some light on this.

Database recovery model: enter image description here

TLOG: enter image description here

From the above snip we can see even though DB is in Full recovery model,only extent allocations are logged.Is this expected.I am seeing the same behaviour for Bulk logged recovery model too with only change being database pages are also backed up.If only extents are logged in full recovery model,how point in time restore capability is acheived.Please let me know if i am missing any thing

1 Answer 1

2

I finally found the answer Red gate transaction log management book by Tony and Gail shaw.

Below is the Snip for a select into operation done in full recovery model:

enter image description here

From the above snip we can see all the operations done to sometable are logged under the context of lop_format_page and logrecordlength is also 8276 bytes which indicates SQl eventhough under full recovery model,doesnt log each and every row,rather it logs entire image of the page plus log headers.

Below is the Snip for the same operation done in Bulk logged recovery model: enter image description here

From the above Snip,we can see now there is no record indicating LOP_FORMAT_PAGE,rather Sql is now tracking only page allocations and extent allocations,plus some internal system table changes,but no data pages are present in the log.

Thanks to redgate for the amazing Book

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.