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.

When used with reserved_threshold, default specifies 85 percent.

When used with allocation_threshold, default 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 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 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

This 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

This 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

Permissions

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

See also

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