Pages

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.

CREATE DATABASE Mydb
GO
ALTER DATABASE Mydb SET RECOVERY BULK_LOGGED
BACKUP DATABASE Mydb TO DISK='D:\mydb.bak'
GO
DBCC TRACEON(3604)
DBCC PAGE('MYDB',1,7,3)






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
DBCC PAGE('MYDB',1,6,3)








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'
GO
DBCC PAGE('MYDB',1,7,3)






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

54 comments:





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

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

    ReplyDelete
    Replies
    1. let's not forget, research is not just about numbers and data; it's about curiosity, passion, and the relentless pursuit of answers. It's about asking the right questions, diving deep into the abyss of information, and resurfacing with a treasure trove of wisdom. game gacor

      Delete
  3. 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

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

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

    ReplyDelete





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

    ReplyDelete

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

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Dengan upaya-upaya seperti itu, maka semuanya sebetulnya bertumpu pada harapan bagaimana caranya agar bisa beruntung dan mendapatkan banyak keuntungan finansial besar dari aktivitas tersebut
    asikqq
    http://dewaqqq.club/
    http://sumoqq.today/
    interqq
    pionpoker
    bandar ceme
    freebet tanpa deposit
    paito warna terlengkap
    syair sgp

    ReplyDelete
  11. Very good and interesting, I appreciate your work. It is extremely nice to see the greatest details presented in an easy and understanding manner.

    Digital marketing training in Delhi
    SEO Institute in Delhi
    Google Digital Marketing Course
    Mobile Repairing Institute Agra

    ReplyDelete
  12. videoder
    videoder app
    videoder apk
    videoder download

    It is extremely nice to see the greatest details presented in an easy and understanding manner.

    ReplyDelete

  13. shareit install
    shareit apk download
    shareit downloading
    It is extremely nice to see the greatest details presented in an easy and understanding manner.

    ReplyDelete
  14. hotmail.com signup

    thanks for sharing great information........

    ReplyDelete
  15. ينبع من المدن المهمة في السعودية ولهذا فإننا وفرنا لأهالي ينبع افضل الخدمات المقدمة في نقل العفش مثل افضل شركة نقل اثاث بينبع التي تقدم اجود خدمات نقل العفش في ينبع باحترافية عالية وقد تحتاج قبل النقل الى التواصل مع اقوى شركه تنظيف شقق بينبع لاجل ان تهتم بأعمال تنظيف منزلك الجديد قبل نقل عفشك اليه ومن الأفضل ان تتصل علي ارخص شركه مكافحه الصراصير بينبع بهدف الحصول على ان تقضي على الحشرات في منزلك الجديد ولتامين المنزل من هجوم الحشرات وقد تحتاج ايضا الى تنظيف خزان الماء وذلك بالتعاقد مع افضل شركات تنظيف الخزانات بينبع لعمل تنظيف وتعقيم الدوري لخزان الماء خاصتك وعمل تعقيم للخزان الأرضي والعلوي ولا بد ايضا ان تهتم بتنظيف المفروشات مع افضل شركه تنظيف موكيت بينبع لتعقيم المنزل او المسجد والمفروشات بالبخار في نفس الموقع

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

    ReplyDelete
  17. https://m.facebook.com/story.php?story_fbid=177591327096281&id=100045362442974

    ReplyDelete
  18. "I would like to say that, your blog is very nice, informative and amazing. Thanks for sharing your blog with us."
    Ready To Repair

    ReplyDelete
  19. Thanks for the post. It was very interesting and meaningful. I really appreciate it! Keep updating stuff like this.
    Data Science
    Selenium
    AWS
    Python Online Classes

    ReplyDelete
  20. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.
    Python Classes in Pune

    Best RPA Training in Pune

    ReplyDelete
  21. Thanks mate. I am really impressed with your writing talents and also with the layout on your weblog. Appreciate, Is this a paid subject matter or did you customize it yourself? Either way keep up the nice quality writing, it is rare to peer a nice weblog like this one nowadays. Thank you, check also event marketing and thank you emails

    ReplyDelete
  22. nice blog
    Our Digital Marketing course in Hyderabad focuses on Making you employeable.

    We make sure you have the right skill to get a job in Digital Marketing.
    digital marketing course in hydrabad

    ReplyDelete
  23. Watch movies online sa-movie.com, watch new movies, series Netflix HD 4K, ดูหนังออนไลน์ watch free movies on your mobile phone, Tablet, watch movies on the web.

    SEE4K Watch movies, watch movies, free series, load without interruption, sharp images in HD FullHD 4k, all matters, ดูหนังใหม่ all tastes, see anywhere, anytime, on mobile phones, tablets, computers.

    GangManga read manga, read manga, read manga online for free, fast loading, clear images in HD quality, all titles, อ่านการ์ตูน anywhere, anytime, on mobile, tablet, computer.

    Watch live football live24th, watch football online, ผลบอลสด a link to watch live football, watch football for free.

    ReplyDelete
  24. في المنطقة الشرقية نمتلك مجموعة فروع خاصة بنقل العفش في كلا من الرياض والدمام والخبر والقطيف والجبيل والاحساء والظهران مثل
    - شركة نقل عفش بالدمام
    - شركة نقل عفش بالخبر
    - شركة نقل عفش بالاحساء
    - شركة نقل عفش بالقطيف
    - شركة نقل عفش بالظهران
    - شركة نقل عفش بالجبيل

    ReplyDelete
  25. في مكة المكرمة نمتلك افضل شركة تقدم خدمات منزلية جيدة مثل خدمات نقل العفش تجدنا ارخص شركه نقل اثاث بمكة المكرمة تقدم خدمات نقل الاثاث باحترافية وتوجد في مكه افضل شركة مكافحة حشرات بمكة التي تقدم خدمات رخيصة وتقدم خدمات جيدة وتمتلك عمالة مدربة ومبيدات حشرية آمنة ومضمونة وفعالة وقد تحتاج الى شركة تنظيف كنب بمكة المكرمة محترفة وهي تقوم بتعقيم منزلك بعد اعمال مكافحة الحشرات داخل المنزل وايضا تحتاج الى احسن شركة تنظيف شقق بمكة المكرمة محترفة للقيام بأعمال تنظيف المنزل سواء كان مفروشا او جديدا وقبل هذا تحتاج الى تنظيف خزان مياهك بالتواصل مع شركات تنظيف الخزانات بمكة التي تهتم وتحافظ على خزانك من التلوث وبالتالي تستخدم مياه نظيفة

    ReplyDelete
  26. Join the best SEO Training Institute in India.Visit: <a href="https://www.bgda.in/>Bharat Go Digital Academy</a>.

    ReplyDelete
  27. Very helpful advice on this article! ufabet168 It is the little changes that make the biggest changes. Thanks a lot for sharing!

    ReplyDelete
  28. impossible to find well-informed people in this particular topic,but you sound like you know what you’re talking about! Skydive Fyrosity Las Vegas
    Columbia Interchange Omni Heat
    skydiving jobs

    ReplyDelete
  29. Thanks in support of sharing such a good thought, piece of writing is
    fastidious, thats why i have read it entirely

    Also visit my web page - 샌즈카지노

    ReplyDelete
  30. Thanks for the share. But if you guys want Top Digital Branding Agency In Delhi then contact us. Candela Gentlemax Pro Laser Hair Removal

    ReplyDelete
  31. Other times, it’s appropriate for the bodyguard to fit in with the situation they’re in. For example, if you like to play tennis or go running for an hour every morning, a large bodyguard may not be so suitable.private security In these circumstances, a more appropriate body

    ReplyDelete
  32. Your blog is filled with unique good articles! I was impressed how well you express your thoughts, Checkout how to edit fce post utme form

    ReplyDelete
  33. Nice post. I used to be checking continuously this weblog and I am inspired! Extremely useful information particularly the final phase. whatsapp mod

    ReplyDelete
  34. R Programming Assignment Help
    Thetutorshelp.com DO you need R Programming Assignment Help? Get best R Studio Assignment Help experts. Get R progrmaming Homework Help from professionals.https://www.thetutorshelp.com/r-programming-assignment-help.php
    <a href="https://www.thetutorshelp.com/r-programming-assignment-help.php

    ReplyDelete

  35. Our reputed nursing ethics assignment in-expert staff has the knowledge and experience to comprehend this subject and, as a result, produce original and authentic material for your assignment. Students that need help finishing their assignments. Our knowledgeable nursing ethics experts are available to assist them in the best way possible. Visit now :-Nursing Ethics Assignment Help

    ReplyDelete
  36. Hello, I am one of the most impressed people in your article. 우리카지노 What you wrote was very helpful to me. Thank you. Actually, I run a site similar to you. If you have time, could you visit my site? Please leave your comments after reading what I wrote. If you do so, I will actively reflect your opinion. I think it will be a great help to run my site. Have a good day.


    ReplyDelete
  37. I've been looking for photos and articles on this topic over the past few days due to a school assignment, 우리카지노 and I'm really happy to find a post with the material I was looking for! I bookmark and will come often! Thanks :D


    ReplyDelete
  38. Delving into the intricacies of SQL Server's Bulk Change Map (BCM) page and the Bulk Logged Recovery Model reveals a world of database management efficiency. Mastering these concepts is a vital step in optimizing data operations and ensuring the integrity of your SQL Server environment. Explore the power of BCM and Bulk Logged Recovery to enhance your database management skills.visit:SAP PP Training in Hyderabad

    ReplyDelete