Thresholds and sp_dumpoptimize

The default values for the thresholds are: Reserved pages: 85%; allocation pages: 40%

If the device fragment of the database has a reserved pages percentage that is:
  • Greater than or equal to the reserved threshold – then all the blocks on this device that pertain to this database are dumped.

  • Less than the reserved threshold – then Backup Server starts checking each allocation unit on this device for the allocation percentage. If the cumulative allocation percentage is:
    • Less than the allocation threshold – then it would only dump those pages with data written on it

    • Greater than the allocation threshold – the entire allocation unit would be dumped.