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


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




  • 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.

See also:
  • dump database, dump transaction, load database, load transaction in Reference Manual: Commands

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


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


With granular permissions enabled, you must be a user with dump database privilege on the database you are dumping.


With granular permissions disabled, you must be the datatype owner, a user with sa_role, or a user with sso_role.


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



Audit option


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