A local system temporary database is the required default temporary database for each instance. You configure it when the cluster is configured or when a new instance is added to the cluster. The instance stores all session-specific temporary objects (such as # tables and worktables) in this database unless you create and use local user temporary databases for the instance. You must create local system temporary databases on shared storage. See also “Using private devices for temporary data”.
In a nonclustered Adaptive Server environment, the system temporary database tempdb (dbid 2) is added to the default temporary database group. In the Cluster Edition, the local system temporary database is not part of the default group for the instance. The local system temporary database is assigned to a session only if the default group for the current instance is empty and no other bindings have been specified.
For the Cluster Edition, the
default temporary database assigned to a session is a local temporary
database for the instance, not the system tempdb (with
a dbid of 2). You may need to modify applications
that, in a nonclustered environment assumed the default assigned
temporary database as system tempdb (with a
dbid of 2) in their actions, so that these actions are now applied to
the assigned local temporary database.
For example, in a nonclustered Adaptive Server, if an application
truncates the log of the default temporary database by:
dump tran tempdb with truncate_only
In the Cluster Edition, you must modify the application to truncate the log of the default assigned temporary database:
declare @tempdbname varchar(30) select @tempdbname = db_name(@@tempdbid) dump tran @tempdbname with truncate_only
The Cluster Edition supports multiple temporary databases, such as user local temporary databases, system local temporary databases, and global temporary databases (that is, tempdb).
The Cluster Edition supports a separate user log cache only for the session’s temporary database, and not for any other temporary databases.
The session’s temporary database can be a:
User local temporary database
System local temporary database
Global temporary database when the cluster is in tempdb configuration mode
During tempdb configuration, when local system or local user temporary databases do not exist, the session’s default temporary database is the system tempdb (with a database ID of 2). You cannot use a separate user log cache for the system tempdb during its configuration.
