Multiple TEMP DBs

Temp DB Creation

The multiple tempdb feature provides users the ability to create temporary database which can be used for the creation of temporary objects

• DBA's can bind any user login's as well as application to the temporary db
• The concept of multiple tempdb's is introduced from the version 12.5.3 and from version 12.5.1 we can bind the users and application to user created temporary db

Syntax : create temporary database dbname on devname=size

Example : create temporary database tempdb on tempdeve= 50m

1>sp_tempdb “add”, “tempdb”, “default”

2>go

• we need to add the tempdatabase to the default group
• once the database has been created this database will be used as an additional temporary database
• sp_tempdb 'show'

Binding application to temporary db

• create temporary database dbname on devname=size

• 1>sp_tempdb 'bind','AP','applname','db_name','tempdb'
• 2>go

Binding user to the temporary db

• create temporary database dbname on devname=size

• 1>sp_tempdb 'bind','LG','sa','DB','tempdb'
• 2>go

UN-Binding Users/Application from user created database

• 1>sp_tempdb 'unbind','AP','applname'
• 2>go

Dropping the temporary database

• 1>drop db dbname
• 2>go