sp_dumpoptimize

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_threshold – default specifies 85 percent.
- 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 – Dumps the whole database:

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 – Dumps only the allocated pages, 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 – Sets the reserved threshold 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 – Dumps disks in the database with a percentage of reserved pages that is greater than or equal to 60 percent 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 – Reads allocation pages for those disks with a reserved page percentage that 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 example 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 – Dumps disks in the database with a percentage of reserved pages that is greater than or equal to 60 percent 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 – 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

When you set a threshold using sp_dumpoptimize, this threshold acts on each individual device that the database resides on.
When you set values with sp_dumpoptimize, those values are immediately in affect without the need to restart Backup Server. However, the changes are effective only until the Backup Server is restarted. When Backup Server is restarted, the default values are used.
If you issue sp_dumpoptimize multiple times, the thresholds specified by the last instance are used by later dumps. For example, if you first set the reserved_threshold value, and later issue archive_space=maximum, then that value overwrites the previous value you set for reserved_threshold.
Dumps of different databases can use different thresholds by changing the sp_dumpoptimize values before each database dump.
The optimal threshold values can vary from one database to another. Therefore, the performance of a dump depends on both the I/O configuration and the amount of used space in the database. The DBA can determine the appropriate configuration for a database by experimenting with dumps using different values and choosing the one that results in the shortest dump time.
You can use sp_dumpoptimize for both local and remote dumps.
sp_dumpoptimize has no effect on the performance of a transaction log dump or a load. Therefore, it need not be issued before dump transaction, load database or load transaction operations.
If sp_dumpoptimize is issued without any parameters, the current value of the thresholds is displayed on the client.
On configurations in which the archive device throughput is equal to or higher than the cumulative throughput of all the database disks, using archive_space=maximum may result in a faster dump. However, on configurations in which the archive device throughput is less than the cumulative throughput of all the database disks, using this option may result in a slower dump.
The option names and the values for this procedure can be abbreviated to the unique substring that identifies them. For example, ar = ma is sufficient to uniquely identify the option archive_space=maximum.
There can be zero or more blank space characters around the equal sign (=) in the option string.
The option names and their values are case insensitive.

Permissions

The permission checks for sp_dumpoptimize differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with`dump database` privilege on the database you are dumping.
Disabled	With granular permissions disabled, you must be the datatype owner, a user with sa_role, or a user with sso_role.

Auditing

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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