SQL Server: Understanding BCM Pages

Let's talk about it today.Bulk Change Mapping (Bulk Changed Map:Bcm) page, and how the bulk-logged Recovery model ( bulk logged recovery model ) works.

bulk change mapping (Bulk Changed map:bcm): SQL Server uses BCM pages to track areas modified by bulk-logged operations since the last full backup. In the database file, the BCM page is page 7th. BCM has one bit for each tracked page. If this bit mark is 1, the corresponding zone is modified after the last log backup because of the bulk-logged operation. If this bit is marked as 0, it indicates that the bulk-logged operation has not been modified since the last day backup. A BCM page can hold information for nearly 64,000 districts. BCM pages are repeated every 511232 pages. A BCM page can track information from 63,904 districts. A 2nd BCM page will appear on page No. 511239.

you can find BCM pages in all databases, but only when the database is in bulk-logged recovery mode. In this recovery model, when a log backup is performed, SQL Server scans the BCM page, where it looks for areas marked as changed when a log backup is made using the transaction log. This makes SQL Server bulk-logged recovery recoverable, if the database is restored from a database backup, and there is a series of transaction log backups. In the bulk-logged recovery model, bulk-logged operations include Bcp,bulkinsert, Select into, and so on in the transaction log, where the log is minimally written, and the modified extents are tracked through the BCM page. This gives SQL Server better performance when it performs bulk operations.

BCM pages are independent of simple restore and full restore mode. In the simple and full restore mode, bulk-logged operations are treated as full log operations. In short, in the simple and bulk-logged restore mode, BCM pages Track changes that occur through bulk-logged operations.

We use an example to understand the following: Create a new empty database, modify the database restore mode for the bulk-logged recovery model, back up the database, and finally use the DBCC page to view the information on the BCM pages.

1 CREATE DATABASEBcmdb2 GO3 ALTER DATABASEBcmdbSETRECOVERY bulk_logged4 BACKUP DATABASEBcmdb to DISK='D:\BCMdb.bak'5 GO6 DBCCTRACEON (3604)7 DBCCPAGE ('Bcmdb',1,7,3)

You can see clearly that you do not see partition changes due to bulk-logged operations. Let's perform a bulk-logged operation.

1 SELECT *  into  from AdventureWorks2008R2.sales.SalesOrderDetail 2 DBCC TRACEON (3604)3DBCC PAGE ('bcmdb',1 ,7,3)

1 BACKUP LOG  to DISK = ' D:\BCMdblog.trn ' 2 GO 3 DBCC PAGE ('bcmdb',1,7,3)

Note that BCM pages do not change as a result of full or differential backups. Transaction log backups are the basis of the transaction log just now. SQL Server should be aware of the changes that have occurred between the 2 transaction logs. If a differential or full backup clears the markup in the BCM page, the next transaction log backup cannot track or restore changes between the previous log backup and the full or differential backup. In short, a full or differential backup does not interrupt the chain of transaction log backups.

SQL Server: Understanding BCM Pages