Multiple Temporary Databases - Revision history

Psap at 14:50, 27 March 2007

2007-03-27T14:50:22Z

Psap at 21:15, 29 December 2006

2006-12-29T21:15:19Z

Psap at 16:54, 29 December 2006

2006-12-29T16:54:51Z

New page

The "Multiple Temporary databases" feature was introduced in ASE 12.5.0.3.

[[Category:ASE]]

@@ Line 1: / Line 1: @@
 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:
@@ Line 19: / Line 23: @@
 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]]

← Older revision		Revision as of 21:15, 29 December 2006
Line 1:		Line 1:
−	The "Multiple Temporary ~~databases~~" feature was introduced in ASE 12.5.0.3.	+	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]]