Sizing the Modified Pages Section

The modified pages section stores modified or newly allocated database pages.

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 segment. The disposable changes segment is discarded at the beginning of each load tran command.

Note: SAP ASE 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.

You can 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; however there are downsides to doing this.

Note: 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, which 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.

Note: You cannot use thresholds to manage the space in the modified pages section.