Difference between revisions of "Multiple Temporary Databases"
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | + | ==Background== | |
| + | The "Multiple Temporary Databases" feature (a.k.a. "multiple tempdb's") was introduced in ASE 12.5.0.3. 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. | ||
[[Category:ASE]] | [[Category:ASE]] | ||
Latest revision as of 15:50, 27 March 2007
Background
The "Multiple Temporary Databases" feature (a.k.a. "multiple tempdb's") was introduced in ASE 12.5.0.3. 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.
