quiesce database

Syntax

quiesce database tag_name hold database_list [for external dump] 
	[to manifest_file [with override]]

or:

quiesce database tag_name release

Parameters

tag_name – is a user-defined name that designates the list of databases to hold or release. The tag_name must conform to the rules for identifiers.
hold – when used with to manifest_file clause, holds the database and creates a manifest file.
Warning! Since the manifest file is binary, operations that perform character translations of the file contents (such as ftp) corrupt the file unless performed in binary mode.
database_list – is the list of the databases included in the quiesce database hold command.
for external dump – specifies that while updates to the databases in the list are suspended, you physically copy all affected database devices, using some facility external to the SAP ASE server. The copy operation serves as a replacement for the combination of dump database and load database.
manifest_file – the binary file that describes the databases that are present on a set of database devices. It can be created only if the set of databases that occupy those devices are isolated, self-contained on those devices.
Since the manifest file is a binary file, operations that can perform character translations of the file contents (such as ftp) corrupt the file unless performed in binary mode.
with override – overrides any restrictions that prevent you from successfully executing quiesce database on a database.

Examples

Example 1 – Suspends update activity on salesdb and ordersdb:
```
quiesce database report_dbs hold salesdb, ordersdb 
```
Example 2 – Resumes update activity on the databases labeled report_dbs:
```
quiesce database report_dbs release
```
Example 3 – Suspends update activity to the pubs2 database and signifies your intent to make an external copy of this database:
```
quiesce database pubs_tag hold pubs2 for external dump
```
Example 4 – Places the database in a hold status and builds the manifest file for a database to be copied to another SAP ASE server:
```
quiesce database pubs_tag hold pubs2 for external dump to 
    "/work2/sybase1/mpubs_file with override
```
Once the command completes, control returns to the user.
Example 5 – Copies the database devices, using the mount database with listonly to list all of the devices to be copied to view:
```
1> mount database all from "/data/sybase2/mfile1" with listonly
2> go
```
```
"/data/sybase1/d0.dbs" = "1dev1"
```
You cannot create a manifest file if the set of databases that are quiesced contain references to databases outside of the set. Use with override option to bypass this restriction:
```
quiesce database pubs2_tag release for external dump to Mpubs_file
```

Example 6 – key_db contains the encryption key used to encrypt columns in col_db:

quiesce database key_tag hold key_db for external
         dump to "/tmp/keydb.dat"

quiesce database encr_tag hold col_db for external dump       to "/tmp/col.dat" with override

quiesce database col_tag hold key_db, col_db for 
    external dump to "/tmp/col.dat"

Usage

quiesce database used with the hold keyword suspends all updates to the specified database. Transactions cannot update data in suspended databases, and background tasks such as the checkpoint process and housekeeper process skip all databases that are in the suspended state.
quiesce database used with the release keyword allows updates to resume on databases that were previously suspended.
quiesce database used with the for external dump clause signifies that you intend to make an external copy of the database.
Recovery for this database may fail or the database may not be consistent If you:
- Quiesce a reduced-durability database (including tempdb)
- Copy the device
- Mount this database on another SAP ASE server
Change a database’s durability with alter database before you issue a quiesce database command.
The quiesce database hold and release commands need not be executed from the same user session.
If the databases specified in the quiesce database hold command contain distributed or multidatabase transactions that are in the prepared state, the SAP ASE server waits during a timeout period for those transactions to complete. If the transactions do not complete during the timeout period, quiesce database hold fails.
If the SAP ASE server is executing a dump database or dump transaction command on a database specified in quiesce database hold, the database is suspended only after the dump command completes.
If you execute a dump database or dump transaction command on a database while updates to the database are suspended, the SAP ASE server blocks those commands until the database is released with quiesce database release.
If you attempt to run a query against a database that is quisced, the SAP ASE server issues error message 880:
```
Your query is blocked because it tried to write and
database '%.*s' is in quiesce state. Your query will
proceed after the DBA performs QUIESCE DATABASE
RELEASE
```
The query is run once the database is no longer in a quiescent state.
To duplicate or copy databases, use quiesce database with the extension for creating the manifest file. The quiesce database effects the quiesce hold by blocking writes in the database, and then creates the manifest file. The command then returns control of the database to the user. You can now use a utility to copy the database to another SAP ASE server. These rules for quiesce database hold must be followed for the copy operation:
- The copy operation cannot begin until the quiesce database hold process has completed.
- Every device for every database in the quiesce database command must be copied.
- The copy process must complete before you invoke quiesce database release.

See also sp_helpdb, sp_who in Reference Manual: Procedures.

Permissions

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `quiesce any database` privilege.
Disabled	With granular permissions disabled, you must be a user with sa_role.

Auditing

Values in event and extrainfo columns of sysaudits are:

Information	Values
Event	96
Audit option	quiesce
Command or access audited	quiesce database
Information in `extrainfo`	Roles – current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – original login name, if a set proxy is in effect