Multiple Temporary Databases

From SybaseWiki
Jump to: navigation, search


The "Multiple Temporary Databases" feature (a.k.a. "multiple tempdb's") was introduced in ASE It's main purpose is twofold:

  • To work around locking issues on the system tables in tempdb.
  • To direct certain users or applications to their own tempdb. This is particularly useful for DBA's who encounter an inaccessable tempdb because it has been filled up completely.

How to implement

Follow these easy steps to create a used defined tempdb:

-- create the database
create temporary database <name> on <device allocation>

-- add the new database to the pool
sp_tempdb "add","<name>","default"

That's all! When you use multiple tempdb's to prevent locking issues on system tables keep all tempdb's the same. Keep the sizes and allocation the same as well as the database options. Tempdb's bound to cache can either be bound to a single cache for all temporary databases, or give each database a separate cache. I would recommend to use a single cache for all temporary databases.

To bind a login to a particular temporary database, use the sp_tempdb stored procedure, like in this example:

sp_tempdb "bind","LG","<login-name>","DB","<database name>",null,null

When you use temporary databases for the first time, don't forget to check your maintenance scripts. A temporary database can distinguised from a normal database with the value of 256 in the status3 column in sysdatabases (status3 & 256 = 256).

Known Bugs

  • Binding different user defined tempdb's to their own cache caused problems in the past, this issue should be solved in 12.5.3
  • Resource limits of type "tempdb_space" do not work correctly in combination with drop and/or truncate table. This should be solved in 12.5.4 ESD #1 and 15.0.2. Thanks to Romy Lodh for reporting this.