Wednesday, 24 July 2013

SQL Server : Understanding Bulk Change Map (BCM) Page and Bulk Logged Recovery Model

In the last post , we have discussed about the Differential Change Map pages and how it is utilized in the differential backup process.In this post, let us try to understand the bulk change map page and how the bulk logged recovery model works.

Bulk Change Map(BCM): SQL Server uses Bulk Change Map (BCM) page to track extent modified by bulk logged operation since last backup log operation. BCM page is the 7th page in the data file.BCM has a bit for every extent it tracks. If the bit is 1, the corresponding extent is modified after the last log backup due to bulk logged operation, if the bit is 0, there is no change in the corresponding extent due to bulk logged operation after the last log backup.A BCM page can hold information of around 64000 extents. Precisely BCM pages repeat after every 511232 pages .One BCM page can track the changes of exactly 63904 extents.The second BCM page will be 511239th page.

You can see the BCM pages in all the databases, but they are only relevant when the database is in the bulk recovery model.In this recovery model, while taking log backup, SQL server scans the BCM page and include the extents that are marked as changed in the log backup along with transaction log.This helps SQL server to make bulk logged operation to be recoverable if the database is restored from database backup and a sequence of transaction log backups.In the bulk logged recovery model, bulk logged operation like BCP,Bulkinsert,Select Into are minimally logged in the transaction log and modified extents are tracked through the BCM pages.This helps SQL server to have optimized performance for bulk operations.

BCM pages are not relevant in simple recovery and full recovery model. In simple and full recovery model, the bulk logged operation are treated as fully logged operation. In short, in simple and bulk recovery model, BCM page will not keep track of the changes happened through bulk logged operations.

Let us try to understand with a sample.


From the output ,it is clear that none of the extents are changed due to bulk logged operations. Let us try to perform a bulk logged operation.

SELECT INTO mydb..SalesOrderDetail FROM AdventureWorks2008.sales.SalesOrderDetail

Now in the output, you can see two entries marked as MIN_LOGGED.The first one is due to the change in the IAM page and second one is the actual data inserted as part of Select Into operation.Now when we take a log backup, SQL server backups the transaction log changes along with extent marked as changed in the BCM file. After the log backup, the bits in the BCM pages are flipped to mark there is no extent changed due to the bulk logged operation. Let us take the log backup.

BACKUP LOG Mydb TO DISK='D:\mydblog.trn'

From the output, we can see that SQL server flipped the bit in BCM page and none of the extents are marked as changed due to the bulk logged operation. Note that BCM pages are not cleared by taking a full backup or differential backup.The base of the transaction log backups is the previous transaction log backup. SQL server should know what changes are happened between two transnational log backup.If a differential or full backup cleared the BCM page,next transaction log backup can not track/recover the changes between the previous log backup and full/differential backup. In short a full backup or differential backup will not break the transaction log backup chain. 

If you liked this post, do like my page on FaceBook


  1. Hi
    I read this post two times.
    I like it so much, please try to keep posting.
    Let me introduce other material that may be good for our community.

    Hadoop training


  2. I loved the way you discuss the topic great work thanks for the share, Let me share this, vmware training in pune

  3. thanks for telling us about these maps programs, you are the best source of reliable information!

  4. ERPTREE is a leading oracle fuison HCM Training Institute. we offer this course through online we have great experience in succeeding students through online courses. we can calculate our performance through their honest comments in our sites in supporting our services. we have referral program so candidates can earn money through referral. you can share your live experience with other can generate you some money.

    Oracle Fusion HCM Training

  5. شركة نقل اثاث بالدمام التفاؤل شركة نقل اثاث بالخبر كما انها افضل شركة نقل اثاث بالجبيل نقل عفش واثاث بالجبيل والخبر والقطيف والدمام
    شركة نقل اثاث بالدمام
    شركة نقل اثاث بالجبيل


  6. شركة نقل عفش واثاث بالدمام ابيات الشرقيه لخدمات نقل العفش والاثاث بالدمام
    شركة نقل عفش بالدمام
    نقل عفش بالخبر
    شركة نقل اثاث الدمام
    نقل عفش الدمام
    نقل عفش بالدمام
    ان اردت نقل عفش منزلك بالدمام ابيات الشرقية من اهم شركات نقل العفش بالدمام والخبر والجبيل والقطيف والاحساء

  7. I do not use a card on my site. I think it's overweight to load the page

  8. I like the post format as you create user engagement in the complete article. It seems round up of all published posts. Thanks for gauging the informative posts.
    cara menggugurkan kandungan

  9. That's a Great Article and more useful for anyone.Thanks for sharing...
    oracle course in chennai


  10. App Cloner Full Versionis an application that will allow you to make exact copies of any app on your smartphone or tablet.

  11. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
    Selenium Training in Bangalore | Selenium Training in Bangalore | Selenium Training in Bangalore | Selenium Training in Bangalore

  12. هل تبحث عن شركة متخصصة فى خدمات التنظيف بالطائف بافضل المعدات والسوائل وثقة تمة فى العمل ودقة فى النتائج كل هذه المميزت توفرها شركة الخليج الشركة الافضل والامثل فى الخدمات المنزلية بالطائف وبما اننا الشركة الافضل والامثل بدون منافس سوف نسعى لتوفر افضل الخدمات باقل تكلفة وبقدر كبير من الاهتمام والدقة عزيزى اينما كنت فى اى منطقة ا وحى تابع لمدينة الطائف اتصل بنا وسوف نصلك فى الحال شركة الخليج للخدمات المنزلية شركة تنظيف منازل بالطائف
    شركة تنظيف فلل بالطائف
    شركة تنظيف خزانات بالطائف
    شركة تسليك مجارى بالطائف
    شركة رش مبيدات بالطائف
    شركة مكافحة نمل ابيض بالطائف
    شركة مكافحة حشرات بالطائف
    شركة عزل اسطح بالطائف
    شركة عزل خزانات بالطائف


  13. tutu app
    tutu app download
    tutu app free
    tutuapp vip
    One of their coolest creations is TutuApp, an application that enhances the gaming experience.

  14. This comment has been removed by the author.

  15. Thanks for giving great kind of information. So useful and practical for me. Thanks for your excellent blog, nice work keep it up thanks for sharing the knowledge.
    Best Interior Designer in Noida

  16. Indoor LED Wall Lights in delhi
    Thanks for giving great kind of information. So useful and practical for me. Thanks for your excellent blog, nice work keep it up thanks for sharing the knowledge.

  17. I feel happy about and learning more about this topic. keep sharing your information regularly for my future reference. This content creates new hope and inspiration within me. Thanks for sharing an article like this. the information which you have provided is better than another blog.
    IELTS Coaching in Dwarka

  18. شركة اللمسة الأخيرة تقدم لك الحل الأمثل فلا حشرات بعد اليوم ولن تعود مرة أخرى. فنحن نستخدم أفضل المبيدات العالمية الفعالة صديقة البيئة التي لا تترك رائحة ولا سيوثر على صحة الأنسان ويقوم باستخدامها عمال مدربون يقومون برش المبيدات بشكل علمي مما يضمن لك الراحة التامة نرجو التواصل على هذا الرقم 0580002467
    شركة رش مبيدات بأبها
    شركة مكافحة حشرات بأبها
    شركة مكافحة النمل الابيض بأبها
    شركة رش مبيدات بخميس مشيط
    شركة مكافحة حشرات بخميس مشيط
    شركة مكافحة النمل الابيض بخميس مشيط
    شركة رش مبيدات بالقصيم
    شركة مكافحة حشرات بالقصيم
    شركة مكافحة حشرات بجازان
    شركة رش مبيدات بجازان


  19. Thankyou for the valuable information.iam very impressed with this one.
    Please do find the below attachment and kindly download latest mod apk form our website for free.

    live tv net
    ac market downloading
    acmarket download
    live tv net app
    live net tv
    mx player apk
    mx player pro
    tubemate apk"
    tiktok apk
    yo whatsapp

  20. A befuddling web diary I visit this blog, it's incredibly grand. Strangely, in this present blog's substance made motivation behind fact and sensible. The substance of information is instructive
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training