The modified pages section is used to store database pages that are modified or newly allocated.
A page can be remapped to the modified pages section only once.
Recovery is responsible for most page remappings.
dbcc checkalloc also requires significant space allocation.
The size of the modified pages section can be increased using the alter database command. However, to decrease the size of the modified pages section, you must drop the archive database and re-create it.
The minimum size of the modified pages section depends on how many pages are modified or newly allocated in the database. Many of these pages are modified by redo recovery and undo recovery.
Use the load database with norecovery command to minimize the number of modified pages and therefore the amount of space required in the modified pages section. There are downsides to doing this. For more information, see “Using load database with norecovery”.
dbcc checkalloc consumes a large
amount of space in the modified pages section, even if you use the nofix option.
When you run dbcc checkalloc, every allocation
page (every 256th page) has information written to it. These allocation-page
modifications are stored in the modified pages section, and mean
that when you are using dbcc checkalloc, you
need modified pages section that is at least 1/256th the
size of the original database.
If you do not have enough space in the modified pages section, the command that requires the space is suspended and you see an error similar to:
There is no more space in the modified pages section for the archive database <database name>. Use the ALTER DATABASE command to increase the amount of space available to the database.
To increase space in the modified pages section, either:
Use alter database to increase the size of the modified pages section, or
If you do not want to allocate additional space to the modified pages section, enter Ctrl+C to abort the current command.
You cannot use thresholds to manage the space in the
modified pages section.
