A while back, I was thinking about my backup strategy and it occurred to me that I might be able to optimize it if I could predict, in advance, how much disk space a differential backup would consume. If a database hasn't changed much - or at all - since yesterday's full backup, why not save space and time by doing incremental backups until it's worthwhile to do another full? Since differential backups run very quickly on even very large databases when little has changed, there must be some structure in the database that accelerates finding the changed pages.

"Surely," I thought, "this information must be available to the DBA."

So, I started hunting through "Books On Line" and trolling through the SQL-DMO Object Model looking for the function that would tell me what percentage of a database had changed through the last full backup or a function that would list the affected pages or ... something useful... but I couldn't find anything obviously helpful.

The best I could find was a brief description of the Differential Change Map (DCM) page, in Kalen Delaney's book, "Inside SQL Server 2000," and a briefer description of how to locate the page in the database.

Armed with this meager store of knowledge, I started working with the DBCC PAGE utility to see if I could locate and decode the information on that page.

I ran into trouble right away. "Inside SQL Server" said that page 6 would be a DCM page and, sure enough, it was. However, it also said that a DCM page occurred every 511230 pages and that wasn't quite the case. Fortunately, the actual value was very close (511232) and I was able to locate the second DCM page in one of my larger databases after a brief search just ahead of and just beyond the expected page. I'm still not sure why the published value is off by two.

To see an example DCM page, execute the following:

DBCC TRACEON (3604)
GO
DBCC PAGE('<database>',1,6,3)
GO

The parameters to this DBCC command are:

DatabaseName

FileNumber

PageNumber

Formatting

I ran this command against a couple of small test databases, trying various formats. As it turns out, a format value of 3 formats a DCM page very nicely. The output (minus a lot of page header information) looked like this:

DIFF_MAP: Extent Alloc Status @0x1A14C0C2
-----------------------------------------
(1:0)        - (1:8)        =     CHANGED           
(1:16)       -              = NOT CHANGED           
(1:24)       -              =     CHANGED           
(1:32)       - (1:56)       = NOT CHANGED           
...

What we see here are extents or extent ranges described by their first page number for a single extent or first page number of first extent and first page number of last extent for a range.

Dividing the page numbers by 8 gives the extent numbers that are marked changed or unchanged. So, for the dbcc ouptut fragment above, we determine that:

Extents 0 to 1 - changed (pages 0 to 7 and 8 to 15)

Extent 2 - not changed (pages 16 to 23)

Extent 3 - changed (pages 24 to 31)

Extent 4 to 7 - not changed (pages 32 to 63)

Awkwardly, DMO and DBCC do not agree on terminology. To tally up all the changed extents for a database, you also have to look through all files in the database. I was planning to discover all the databases and files on a server by looping through DMO objects. In DMO, a database can have one or more File Groups, referenced by FileGroupNumber. I had to do a little experimentation to be sure that a FileNumber parameter for DBCC would be the same as FileGroupNumber in DMO and it is.

The next step was to try and tally up the list of changed extents, run a differential backup and see how my tally compared to reality. Even tallying a small-but-still-large-enough database for test purposes would require me to get started with programming at this point, rather than spend a lot of time with a calculator.

My plan was to use VB to execute the DBCC calls and get the output from the 3604 trace flag into a message buffer. Unfortunately, the there's considerable gark included with the output, along with some seemingly unpredictable extra cr/lf pairs, and parsing this looked like it was going to be a true pain in the...

Then I got unlucky and lucky at the same moment. While I was thinking about how I'd do the decoding and tallying, I noticed that the largest message buffer returned was just about 64K in length and quite a few of the message buffers returned were that size. That seemed highly suspicious. A little more investigation persuaded me that, between SQL-DMO and VB, something was limiting the maximum size of the return message buffer to 64K (I use VB 6). If the call wouldn't return the entire message buffer of page information, there was no point writing a ton of code to plow through it. This seemed very unlucky. Thinking there was no feasible way to do this, I set the project aside for the rest of the day.

The next day, I realized that hitting the 64K limit was actually a lucky thing. It caused me to stop and think. I realized that I had foolishly overlooked the "with tableresults" option to the DBCC command. I tried that and found using a result table was more promising. Still using 3 as the format parameter, we get a nicely formatted group of records to scroll through that looks (minus the header records) like this:

DIFF_MAP: Header @0x1A14C064 Slot 0, Offset 96 DIFF_MAP: Extent Alloc Status @0x1A14C0C2 (1:0)  - (1:8) CHANGED
DIFF_MAP: Header @0x1A14C064 Slot 0, Offset 96 DIFF_MAP: Extent Alloc Status @0x1A14C0C2 (1:16) -       NOT CHANGED
DIFF_MAP: Header @0x1A14C064 Slot 0, Offset 96 DIFF_MAP: Extent Alloc Status @0x1A14C0C2 (1:24) -       CHANGED
DIFF_MAP: Header @0x1A14C064 Slot 0, Offset 96 DIFF_MAP: Extent Alloc Status @0x1A14C0C2 (1:32) - (1:56)NOT CHANGED

Using the table result, it was fairly easy to set up a program to scroll through the result set and select only the records marked "CHANGED" and tally up the changed extents. My code examines the field that contains "CHANGED" or "NOT CHANGED" and, when it finds a match on "CHANGED" the page range is parsed and the modified extent(s) are added to the tally.

Then, the final step was to compare the results to reality. A small VB program modeled on the above, scanned 25GB of databases on our server in well under a minute and I arranged to write out the change tallies to a listbox. Running this just before our periodic differential backups, I found my differential size prediction was within 256K of every differential backup. Apparently, each differential includes 128K to 256K of header information, which accounts for the small difference. It also appears that the differential backups are done by extent; if the extent is marked changed, the entire extent is written to the backup.

OK, what good is this? Well, I'm working on that. With reliable differential size prediction, I can set up optimized backup processes that will minimize the number of backup pages written to disk while still maximizing the protection given to the databases. Most of us run some sort of automatic database backup process on a scheduled basis. Knowing the size of an expected differential (and it takes just a very few seconds to get this information), your periodic automatic backups could include code to "decide" whether or not to run a differential or a full backup. If a differential will be truly small, a full backup plus a number of differential backup will both minimize disk space taken for backups and allow for quick restores. Databases which change little over the course of an entire week could be differentialed every day for a week, saving considerable time spent running disk backups. If one of these databases suddenly undergoes massive changes, the next and all subsequent differentials would all be very large. Doing a full at this point and then switching back to differentials might save considerable disk space on the next few days' worth of backups.

However, at this point, I haven't quite figured out how I'm going to set up the automatic process. I'll probably end up with a system that contains history information, so the backup routine can make good guesses as to best strategy and I might include override options the DBA can set to force certain behaviors.

While you're waiting for me to figure out how to use differential prediction in practice, you could look into it on your own. I'm including three files that provide just about all the code you need for a complete differential predictor:

• DifferentialPredictorMain.frm, which is principally the code to loop through the databases on a server and write the predictions to a listbox. The complete form will need about four objects on it, which are described in the comments.
• DBAUtil.bas, which is a module that does most of the heavy lifting in this project. It includes the routines to locate the DCM pages and decode the results.
• UTIL.bas, which contains a few of my library routines I found useful for this project.

You will need to include references to the SQL-DMO objects in your project. As a security note, my code is set up for trusted connections.