The dbid of a newly created temporary database is automatically registered in a global list of all available temporary databases. You cannot bind objects to that database until after it is registered in the global list. When a server is restarted, temporary databases are added to the global list as they are recovered.
The number of temporary databases that is supported is statically declared and is not configurable. The number of temporary databases that can be registered in the global list of temporary databases available for bindings (and, consequently, assignment to sessions) is 512, including tempdb.
When the global list is full, an attempt to add a temporary database to it results in a warning. To see the contents of the list, execute the dbcc pravailabletempdbs command. See “dbcc pravailabletempdbs” for more information.
You can create a database even if you cannot register its dbid in the global list. If the global list is full, you can run a dbcc addtempdb command to add the dbid to the global list when space becomes available. See “dbcc addtempdb” for more information on the dbcc addtempdb command.
In addition, if space becomes available on the global list when a temporary database has been dropped, you can also:
Drop and re-create the temporary database, at which point it gets registered and becomes available for binding, using space that has become available in the global list.
Restart the server.
The system tempdb, which has a dbid of
2, is registered in the global list when the server is restarted.
It cannot be unregistered.
Adaptive Server assumes that the temporary databases you create are not bound to the default group. To add a new database to the default group, use sp_tempdb—see the entry for sp_tempdb in Reference Manual: Stored Procedures for more information. Adding a database to the group makes the database immediately available for round-robin assignment from within the group.Even if the database is not a member of the default group, you can still assign it to a session via an application or login binding. See the bind option in the entry for sp_tempdb in Reference Manual: Stored Procedures for more information for more information.
