sp_dumpoptimize

Description

Specifies the amount of data dumped by Backup Server during the dump database operation.

Syntax

sp_dumpoptimize [ ’archive_space = {maximum | minimum | default }’ ]
sp_dumpoptimize [ ’reserved_threshold = {nnn | default }’ ]
sp_dumpoptimize [ ’allocation_threshold = {nnn | default }’ ]

Parameters

archive_space

specifies the amount of the database you want dumped.

maximum

dumps the whole database without determining which pages are allocated or not. The total space used by the archive image or images is equal to the size of the database. Using this option has the same effect as using the options reserved_threshold=0 and allocation_threshold=0.

minimum

dumps only the allocated pages, which results in the smallest possible archive image. This option is useful when dumping to archive devices for which the throughput is much smaller than that of the database devices such as QIC tape drives. Using this option has the same effect as using the options reserved_threshold=100 and allocation_threshold=100.

default

specifies that default values should be used. When used with:

  • archive_space – this option dumps the database with the reserved_threshold and allocation_threshold options set to their default values. Use this to reset Backup Server to the default configuration.

  • reserved_thresholddefault specifies 85 percent.

  • allocation_thresholddefault specifies 40 percent.

reserved_threshold

dumps all the pages belonging to the database in a database disk if the percentage of reserved pages in the disk is equal to or greater than nnn. For example, if you specify nnn as 60 and if a database disk has a percentage of reserved pages equal to or greater than 60 percent, then the entire disk is dumped without determining which pages within that disk are allocated. The default for this option is 85 percent.

nnn

an integer value between 0 and 100 that represents the value of the threshold. It is used to determine how much data to dump.

When used with reserved_threshold, if the percentage of reserved pages in the disk is greater than the value specified, all the pages of the database in a database disk are dumped.

When used with allocation_threshold, if the percentage of allocated pages in an allocation unit is greater than the percentage specified for allocation_threshold, all the pages within an allocation unit are dumped.

allocation_threshold

dumps all the pages in the allocation unit if the percentage of allocated pages in the unit is equal to or greater than nnn. For example, if nnn is specified as 70 and if the percentage of allocated pages in an allocation unit is equal to or greater than 70 percent, then the entire allocation unit is dumped without determining whether pages within that allocation unit are allocated or not. If the reserved_threshold setting causes the whole disk to be dumped, the allocation_threshold setting is ignored for the disk. The default for this option is 40 percent.

Examples

Example 1

This causes the whole database to be dumped:

sp_dumpoptimize 'archive_space=maximum'
Backup Server: 4.172.1.1: The value of 'reserved pages threshold' has been set to 0%.
Backup Server: 4.172.1.2: The value of 'allocated pages threshold' has been set to 0%.

Example 2

This causes only the allocated pages to be dumped, thereby resulting in the smallest archive image:

sp_dumpoptimize 'archive_space=minimum'
Backup Server: 4.172.1.1: The value of 'reserved pages threshold' has been
set to 100%.
Backup Server: 4.172.1.2: The value of 'allocated pages threshold' has been
set to 100%.

Example 3

This causes the reserved threshold to be set to 85 percent and the allocation threshold to be set to 40 percent:

sp_dumpoptimize 'archive_space=default'
Backup Server: 4.172.1.1: The value of 'reserved pages threshold' has been
set to 85%.
Backup Server: 4.172.1.2: The value of 'allocated pages threshold' has been
set to 40%.

Example 4

Those disks in the database with a percentage of reserved pages that is greater than or equal to 60 percent are dumped without reading allocation pages on this disk. For the remaining disks, the allocation pages are read, and the last set value for the allocation_threshold is used. If the allocation_threshold was not set after Backup Server was started, default allocation_threshold of 40 percent is used:

sp_dumpoptimize 'reserved_threshold=60'
Backup Server: 4.172.1.3: The value of 'reserved pages threshold' has been
set to 60%.

Example 5

Causes the reserved threshold to be set to 85 percent. It does not affect the allocation page threshold:

sp_dumpoptimize 'reserved_threshold=default'
Backup Server: 4.172.1.3: The value of 'reserved pages threshold' has been
set to 85%.

Example 6

Allocation pages are read for those disks whose reserved page percentage is less than the last set value for the reserved_threshold and if an allocation unit has 80 percent or more pages allocated, then the whole allocation unit is dumped:

sp_dumpoptimize 'allocation_threshold=80'
Backup Server: 4.172.1.4: The value of 'allocated pages threshold' has been
set to 80%.

Example 7

Causes the allocation page threshold to be set to the default of 40 percent. It does not affect the reserved pages threshold:

sp_dumpoptimize 'allocation_threshold=default'
Backup Server: 4.172.1.4: The value of 'allocated pages threshold' has been
set to 40%.

Example 8

Those disks in the database whose percentage of reserved pages is greater than or equal to 60 percent are dumped without reading allocation pages on this disk. For the remaining disks, the allocation pages are read and if an allocation unit has 30 percent or more pages allocated, then the whole allocation unit is dumped:

sp_dumpoptimize 'reserved_threshold=60', 'allocation_threshold=30'
Backup Server: 4.172.1.3: The value of 'reserved pages threshold' has been
set to 60%.
Backup Server: 4.172.1.4: The value of 'allocated pages threshold' has been
set to 30%.

Example 9

This displays the current value of the thresholds:

sp_dumpoptimize
Backup Server: 4.171.1.1: The current value of 'reserved pages threshold'
is 60%
Backup Server: 4.171.1.2: The current value of 'allocated pages threshold'
is 30%.

Usage


Thresholds

The default values for the thresholds are:

If the device fragment of the database has a reserved pages percentage that is:

Permissions

Only the System Administrator, the Database Owner, or users with the Operator role can execute sp_dumpoptimize.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Documents See the System Administration Guide for information on allocation pages.

Commands dump database, dump transaction, load database, load transaction