Determining How Much Data Has Changed

If you’re not sure whether the data in your database changes a lot or a little, you can use the undocumented (and therefore unsupported) DBCC PAGE command to retrieve information from the differential bitmap page and display it in a readable format. The command’s syntax is:

DBCC PAGE ( {'dbname' | dbid}, filenum,  pagenum [, printopt={0|1|2|3} ])

First, you need to identify the pages containing the differential bitmap page. To return information about the page header and each individual row, run the DBCC PAGE command with the filenum argument set to 1, the pagenum argument set to 0, and the printopt argument set to 3:

DBCC PAGE (AdventureWorks,1,0,3)  WITH TABLERESULTS, NO_INFOMSGS

Figure A: Results of the initial DBCC PAGE query

Figure A shows the results. As you can see, the first differential bitmap page is page 6 of the first file in the database, so its page ID is (1:6). The VALUE column in Figure A shows that it has changed since the last differential base backup, so you can run the DBCC PAGE command with filenum set to 1, pagenum set to 6, and printopt set to 3:

DBCC PAGE (AdventureWorks,1,6,3)  WITH TABLERESULTS, NO_INFOMSGS

Figure B: Results of the DBCC PAGE query on PageID (1:6)

Figure B shows the results. The ParentObjects column shows DIFF_MAP for all extents covered by this GAM. The Field column shows pages grouped in extents, and the VALUE column indicates whether the extents have changed or not changed. From here, you can calculate the amount of data that has changed and estimate the size of the differential backup.

In the blog “New script: How much of the database has changed since the last full backup?”, Paul Randal provides a script that uses DBCC PAGE to calculate the percentage of data changed across an entire database since the last full backup. This information can help you decide whether to perform a full backup or differential backup.