number of open databases

Summary information
Default value	12
Range of values	5–2147483647
Status	Dynamic
Display level	Basic
Required role	System Administrator

number of open databases sets the maximum number of databases that can be open simultaneously on Adaptive Server.

When you calculate a value, include the system databases master, model, sybsystemprocs, and tempdb. If you have installed auditing, include the sybsecurity database. Also, count the sample databases pubs2 and pubs3, the syntax database sybsyntax, and the dbcc database dbccdb if they are installed.

If you are planning to make a substantial change, such as loading a large database from another server, you can calculate an estimated metadata cache size by using sp_helpconfig. sp_helpconfig displays the amount of memory required for a given number of metadata descriptors, as well as the number of descriptors that can be accommodated by a given amount of memory. A database metadata descriptor represents the state of the database while it is in use or cached between uses.

Optimizing the number of open databases parameter for your system

If Adaptive Server displays a message saying that you have exceeded the allowable number of open databases, you will need to adjust the value.

To set the number of open databases parameter optimally:

Step 1: Determine the total number of databases (database metadata descriptors).
Step 2: Reset number of open databases to that number.
Step 3: Find the number of active databases (active metadata descriptors) during a peak period.
Step 4: Reset number of open databases to that number, plus 10 percent.

The following section details the basic steps listed above.

Use the sp_countmetadata system procedure to find the total number of database metadata descriptors. For example:
```
sp_countmetadata "open databases"
```
The best time to run sp_countmetadata is when there is little activity on the server. Running sp_countmetadata during a peak time can cause contention with other processes.

Suppose Adaptive Server reports the following information:
```
There are 50 databases, requiring 1719 Kbytes of memory. The 'open databases' configuration parameter is currently set to 500.
```
Configure number of open databases with the value of 50:
```
sp_configure "number of open databases", 50
```
This new configuration is only a start; the ideal size should be based on the number of active metadata database cache descriptors, not the total number of databases.

During a peak period, find the number of active metadata descriptors. For example:

sp_monitorconfig "open databases"

Usage information at date and time: Apr 22 2002  2:49PM.
Name              num_free   num_active   pct_act     Max_Used   Reused
--------------    --------    ---------   --------    --------   ------
number of open    50         20           40.00       26         No

At this peak period, 20 metadata database descriptors are active; the maximum number of descriptors that have been active since the server was last started is 26.

See sp_monitorconfig in the Reference Manual for more information.

Configure number of open databases to 26, plus additional space for 10 percent more (about 3), for a total of 29:
```
sp_configure "number of open databases", 29
```

If there is a lot of activity on the server, for example, if databases are being added or dropped, run sp_monitorconfig periodically. You will need to reset the cache size as the number of active descriptors changes.