Have you ever wondered how SQL Server keeps track of the changes that are made after a full backup to take a differential backup? Or how bulk logged changes are taken care of?
Usually we skip these two types of pages in SQL Server. Bulk-logged Change Mapping Page keeps track of any Bulk changes made when the database is changed to Bulk Logged Recovery model.
Differential Changed Mapping Page keeps track of changed pages after the last full backup. So while we take a differential backup all the pages referred in DCM pages will be backed up.
In general a BCM or DCM page can keep track of next 511,230 pages (tracks extents) accounting to approximately 4GB of data. So you can have a BCM or DCM page once every 511,230 pages.
Happy Learning.
Usually we skip these two types of pages in SQL Server. Bulk-logged Change Mapping Page keeps track of any Bulk changes made when the database is changed to Bulk Logged Recovery model.
Differential Changed Mapping Page keeps track of changed pages after the last full backup. So while we take a differential backup all the pages referred in DCM pages will be backed up.
In general a BCM or DCM page can keep track of next 511,230 pages (tracks extents) accounting to approximately 4GB of data. So you can have a BCM or DCM page once every 511,230 pages.
Happy Learning.
DCM keeps track of all the extents that have been modified since the last full backup and not the pages.Its benefit is this greatly reduces the number of pages that a differential backup must scan.
ReplyDeleteHi Kulpreet, I think it tracks the pages and not extents.But I need to reverify on that before I can strongly support or disagree with your point.
ReplyDeleteYou are right Kulpreet.Both BCM and DCM pages keeps track of changes in extents. Correcting my blog and thanks for reading and correcting me. :)
ReplyDelete