The modified pages section is used to store database pages that are modified or newly allocated. These pages are stored in either the permanent changes segment, the disposable changes segment, or both.
A page can be remapped to the permanent changes section only once.
A page can be remapped to the disposable changes section only once.
Recovery is responsible for most page remappings.
dbcc checkalloc also requires significant space allocation.
You can increase the size of the modified pages section 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 permanent and disposable changes segments are logically distinct. The permanent changes segment is defined by the set of sysusages fragments that contain a segmap to the permanent changes segment. The disposable changes segment is defined by the set of sysusages fragments containing a segmap to the disposable changes section. The disposable changes segment is discarded at the beginning of each load tran command.
Adaptive Server automatically manages the division of
space in the modified pages section between the permanent changes
segment and the disposable changes segment. When this automatic
resizing is done, sysusages rows are updated
in the master database.
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.
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 means
that when you are using dbcc checkalloc, you
need a 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.
