Difference between revisions of "Multiple Temporary Databases"
| Line 1: | Line 1: | ||
| − | The "Multiple Temporary | + | 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. | ||
| + | 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). | ||
[[Category:ASE]] | [[Category:ASE]] | ||
Revision as of 22:15, 29 December 2006
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.
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).
