Summary information |
|
|---|---|
Default value |
12 |
Range of values |
6 –2147483647 |
Status |
Dynamic |
Display level |
Basic |
Required role |
System administrator |
Configuration groups |
Memory Use, Meta-Data Caches, SQL Server Administration |
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, use sp_helpconfig to 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
If Adaptive Server displays a message saying that you have exceeded the allowable number of open databases, adjust the value.
Use sp_countmetadata to find the total number of database metadata descriptors:
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 number is only a starting point; base the ideal size 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:
sp_monitorconfig "open databases"
Usage information at date and time: Apr 22 2002 2:49PM. Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name ------------------------ ---------- ---------- ------- ----------- ---------- -------------------- number of open 50 20 .00 26 0 NULL
In this example, 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: Procedures 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, periodically run sp_monitorconfig. Reset the cache size as the number of active descriptors changes.
