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

100 comments:

  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

    ReplyDelete




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

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

    ReplyDelete
  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

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

    ReplyDelete
  6. 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

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

    ReplyDelete





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

    ReplyDelete
  9. 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

    ReplyDelete

  10. 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
  11. This comment has been removed by the author.

    ReplyDelete
  12. 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.

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

    ReplyDelete
  14. Buy the best garden sheds nz online in Newzealand. We provide you quality products at low prices. Treasurebox
    also provide you home delievery services in Auckland Newzealand.

    ReplyDelete
  15. Alleyaaircool is the one of the best home appliances repair canter in all over Delhi we deals in repairing window ac, Split ac , fridge , microwave, washing machine, water cooler, RO and more other home appliances in cheap rates

    Window AC Repair in vaishali
    Split AC Repair in indirapuram
    Fridge Repair in kaushambi
    Microwave Repair in patparganj
    Washing Machine Repair in vasundhara
    Water Cooler Repair in indirapuram
    RO Service AMC in vasundhara
    Any Cooling System in vaishali
    Window AC Repair in indirapuram

    ReplyDelete
  16. Get the best nursing services baby care services medical equipment services and allso get the physiotherapist at home in Delhi NCR For more information visit our site

    nursing attendant services in Delhi NCR
    medical equipment services in Delhi NCR
    nursing services in Delhi NCR
    physiotherapist at home in Delhi NCR
    baby care services in Delhi NCR

    ReplyDelete
  17. 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
  18. 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
  19. videoder
    videoder app
    videoder apk
    videoder download

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

    ReplyDelete

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

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

    شركة غسيل موكيت بابها
    شركة عزل خزانات بابها
    شركة تنظيف خزانات المياه بابها
    شركة تنظيف كنب بابها
    شركة رش مبيدات بابها
    شركة رش مبيدات بابها
    شركة مكافحة الحشرات بابها

    ReplyDelete
  22. hotmail.com signup

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

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

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

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

    ReplyDelete
  26. "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
  27. 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
  28. 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
  29. 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
  30. 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
  31. 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
  32. SamudraBet Situs Judi Online Terbaik, Terpercaya dan Terbesar di Indonesia. Menyediakan permainan :

    Judi bola terbaik

    Judi slot online terpercaya

    Poker online terbaru

    Live casino online

    Judi sabung ayam

    ReplyDelete
  33. เราคือผู้นำด้านเกมพนันออนไลน์ Major168 เราคือผู้ให้บริการ คาสิโนออนไลน์ ที่ได้รับรองว่าดีที่สุดในประเทศไทย มีค่ายเกมส์ให้เล่นมากมาย Sagaming, Sexy bacarat, Dreamgame, Ebet, Wm casino, Vivo gaming ไม่ผ่านเอเย่นต์ ระบบปลอดภัยมีทีมงานดูแลตลอด 24ชม.

    SAGAME88 แหล่งรวมเกมส์พนันออนไลน์ คาสิโนสด บาคาร่า กำถั่ว คาสิโนออนไลน์ ไฮโล รูเล็ต รับเครดิตฟรีเล่นได้ทุกเกมส์ โบนัสสมาชิกใหม่เพียบ พร้อมระบบฝากถอนออโต้ 10วิ เรามีทุกค่ายเกมส์ให้คุณเลือกเดิมพัน SA Game Sexy bacarat Dreamgame WM Casino VIVO Gaming Ebet เล่นได้ทุกเกมส์

    Our website ufabet provides betting services in the system of Auto Deposit-Withdrawal. Our members are not only in Thailand. Online football betting UEFA Bet market And this makes a guarantee that Ufabet168 is another reliable football betting website

    ufa through the website UFABET1688 a web gambling online , one that integrated all the bets from online casinos , online casino , online , and also have a game a lot more to be chosen to play such games.

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

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

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

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

    ReplyDelete
  38. 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
  39. نقدم لكم افضل شركة نظافة عامة بالخرج والمناطق المجاورة لها تحت اشراف افضل العمال والفنيين في مجال التنظيف والغسيل بافضل المعدات والاجهزة المميزة.
    شركة تنظيف بالخرج
    شركة تنظيف منازل بالخرج
    شركة تنظيف فلل بالخرج

    شركة تنظيف مجالس بالخرج
    شركة تنظيف موكيت بالخرج

    شركة تنظيف خزانات بالخرج

    ReplyDelete
  40. 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
  41. 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
  42. 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
  43. Good Article, keep writing more,

    Digital Marketing, Digital Marketing Online Training, Digital Marketing Training Programs, Women Entrepreneurship, Women Entrepreneurship Training Programs, Digital marketing online video course, Women Entrepreneurship Online Certification Course, Business coaching, Training for Business owners, Business coaching for women, young entrepreneurs training

    https://www.eminentdigitalacademy.com

    Digital Marketing Online Training

    ReplyDelete
  44. 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
  45. Nice post. I used to be checking continuously this weblog and I am inspired! Extremely useful information particularly the final phase. whatsapp mod

    ReplyDelete
  46. 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

  47. 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
  48. 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
  49. Decent data, profitable and phenomenal outline, as offer well done with smart thoughts and ideas, bunches of extraordinary data and motivation, both of which I require, on account of offer such an accommodating data here 토토사이트

    ReplyDelete
  50. 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
  51. I finally found great post here. Thanks for the information. Please keep sharing more articles. 바카라사이트

    ReplyDelete
  52. In the meantime, I wondered why I couldn't think of the answer to this simple problem like this. Your article is an article that gives the answer to all the content I've been contemplating. 바카라사이트

    ReplyDelete
  53. Your blog is really nice. Its sound really good 카지노사이트

    ReplyDelete
  54. Meteor88 merupakan situs judi online terpercaya yang menyediakan permainan judi online uang asli terlengkap di Indonesia. Meteor88 Menyediakan permainan Judi Online paling lengkap diantaranya : Sportsbook, Slot Online, Live Casino, Poker Online, Judi Tangkas, Tembak Ikan, Sabung Ayam dan Togel Online. Semuanya bisa di mainkan hanya dengan 1 User ID. Selain menyediakan permainan judi online yang begitu lengkap, meteor88 juga menyediakan Bonus yang sangat menarik untuk para membernya, diantaranya : Bonus New Member 100%, Bonus Deposit Harian 10%, Bonus Cash Back 15%, Bonus Rollingan 1% dan Bonus Refferal sebesar 20%.

    Untuk Informasi lebih lanjut silahkan kunjungi link di bawah ini :

    Judi bola online resmi

    Judi slot online terpercaya

    Judi live casino terbaik

    Judi poker online terbaik

    Judi tembak ikan terbaru

    Judi sabung ayam online

    ReplyDelete
  55. Do you like the kind of articles related to 메이저놀이터 If someone asks, they'll say they like related articles like yours. I think the same thing. Related articles are you the best.

    ReplyDelete
  56. I’m very pleased to discover this site. I want to to thank you for ones time for this particularly wonderful read!! I definitely savored every part of it and i also have you saved as a favorite to see new information on your blog. 먹튀사이트

    ReplyDelete
  57. Learning to program can be difficult, and students may require programming assistance. Many websites provide programming assignment help, and these websites provide useful tips and tactics to help students learn programming quickly. Students should begin with the fundamentals and work their way up while honing their skills. This will aid in the development of a strong core and the instillation of confidence in kids. Students can get assistance from programming assignment help websites if they are stuck with any online programming help. In exchange for their services, these programming help websites demand a fee.

    ReplyDelete