sp_sysmon  sp_thresholdaction

Chapter 1: System Procedures

sp_tempdb

Description

sp_tempdb allows users to:

These bindings are stored in the sysattributes table in master database.

sp_tempdb provides the binding interface for maintaining bindings in sysattributes that are related to the multiple temporary database.

Syntax

sp_tempdb [
	[ { “create” | “drop” } , “groupname” ] | 
	[ { “add” | “remove” } , “tempdbname”, “groupname” ] | 
	[ { “bind”, ““objtype”, “objname”, “bindtype”, “bindobj” 
		[, “scope”, “hardness” ] } | 
		{ “unbind”, “objtype”, “objname” [, “scope” ] } ] | 
	[ “unbindall_db”, “tempdbname” ] | 
	[ show [, "all" | "gr" | "db" | "login" | "app" [, “name” ] ] | 
	[ who, “dbname” ] 
	[ help ] ]

Parameters

create

creates the default temporary database group.

drop

drops a database group.

groupname

is the default database group. Use “default”.

add

adds temporary databases to the default temporary database group.

remove

removes temporary databases from the default temporary database group.

tempdbname

is the name of the temporary database you are adding or removing.

bind

binds logins and applications to temporary databases or the default temporary database group.

unbind

unbinds logins and applications to temporary databases or the default temporary database group.

objtype

is the object type. Valid values are:

Values are not case-sensitive.

objname

is the name of the object you bind or unbind.

bindtype

is the bind type. Valid values are:

Values are not case-sensitive.

bindobj

is the name of the object being bound, and is either a group or a database depending on the bindtype.

scope

NULL.

hardness

is hard, soft, or NULL. The default is soft.

When you set the value of hardness to hard, a failure to assign a temporary database according to the binding results in a failure of the login. When you set the value to soft, such a failure results in the assignment of the system tempdb.

unbindall_db

removes all login and application bindings for a given temporary database. It does not remove any database to group memberships. The tempdbname variable is required with this option.

Existing assignments to active sessions are not affected by this operation.

show

displays information stored in the sysattributes table about the existing groups, group members, login and application bindings, and active sessions that are assigned to a given database. The values are:

Notetempdb is always part of the default database group.

who

displays all active sessions assigned to the given temporary database. When using the who parameter, you must use:

help

displays usage information. Executing sp_tempdb without specifying a command is the same as executing sp_tempdb “help”.

Examples

Example 1

Adds mytempdb1 to the default group:

sp_tempdb "add", "mytempdb1", "default"

Example 2

Removes mytempdb1 from the default group:

sp_tempdb "remove", "mytempdb1", "default"

Example 3

Binds login “sa” to the default group:

sp_tempdb "bind", "lg", "sa", "GR", "default"

The value for objtype in this example is login_name. You can substitute login_name with lg or LG.

The value for bindtype in this example is group. You can substitute group with gr or GR.

Example 4

Changes the previous binding of login “sa” from the default group to mytempdb1:

sp_tempdb "bind", "lg", "sa", "DB", "mytempdb1"

The value for bindtype in this example is database. You can substitute database with db or DB.

Example 5

Binds isql to mytempdb1:

sp_tempdb "bind", "ap", "isql", "DB", "mytempdb1"

The value for objtype in this example is application_name. You can substitute application_name with ap or AP.

Example 6

Changes the previous binding of isql from mytempdb1 to the default group:

sp_tempdb "bind", "ap", "isql", "GR", "default"

Example 7

Removes the bindings of login “sa” and application “isql”.

sp_tempdb "unbind", "lg", "sa"
sp_tempdb "unbind", "ap", "isql"

Example 8

Removes all login and application bindings for the mytempdb1 database:

sp_tempdb "unbindall_db", "mytempdb1"

Example 9

Demonstrates the sp_temp show command. A selection of the different variations is chosen, and abbreviated sample output is displayed.

sp_tempdb show
Temporary Database Groups 
------------------------------- 
default 

Database                        GroupName 
------------------------------- ----------------
tempdb                          default 
mytempdb                        default 
mytempdb1                       default 
mytempdb2                       default 
mytempdb3                       default 

Login   Application  Group    Database    Hardness
------- ------------- -------- ----------- -------- 
NULL   isql          default  NULL        SOFT 
sa      NULL          NULL     mytempdb3   HARD 

Example 10

Displays the default temporary database group:

sp_tempdb show, "gr"
Temporary Database Groups
-------------------------------
default

Example 11

Displays all the temporary database group names that are bound to the default group:

sp_tempdb show, "gr", "default"Member Databases
------------------------------- 
tempdb
mytempdb
mytempdb1
mytempdb2
mytempdb3

Example 12

Displays all the databases-to-group memberships:

sp_tempdb show, "db"
Database              Group
--------------------- ----------------
tempdb                default
mytempdb              default
mytempdb1             default
mytempdb2             default
mytempdb3             default

Example 13

Displays all the databases-to-group memberships for the mytempdb1 database.

sp_tempdb show, "db", "mytempdb1"
Database              Group
--------------------- ----------------
mytempdb1             default

Example 14

Displays all the login bindings where login is not NULL:

sp_tempdb show, "login"
Login   Application   Group   Database    Hardness
------- ------------- ------- ----------- --------

sa      NULL          NULL    mytempdb3   HARD

Example 15

Displays all active sessions that are assigned to the system tempdb:

sp_tempdb who, "tempdb"
spid   loginame
------ ------------------------------
2      NULL
3      NULL
4      NULL
5      NULL
6      NULL
7      NULL
8      NULL

Example 16

Displays all active sessions that are assigned to the mytempdb3 user-created temporary database:

sp_tempdb who, "mytempdb3"
spid   loginame
------ ------------------------------ 
17      sa

Example 17

Displays usage information:

sp_tempdb help
Usage: 
sp_tempdb 'help' 
sp_tempdb 'create', <groupname> 
sp_tempdb 'drop', <groupname> 
sp_tempdb 'add', <tempdbname>, <groupname> 
sp_tempdb 'remove', <tempdbname>, <groupname> 
sp_tempdb 'bind', <objtype>, <objname>, <bindtype>, <bindobj>, <scope>, 
          <hardness> 
sp_tempdb 'unbind', <objtype>, <objname>, <scope> 
sp_tempdb 'unbindall_db', <tempdbname> 
sp_tempdb 'show', <command>, <name> 
sp_tempdb 'who', <dbname> 

<objtype> = ['LG' ('login_name') | 'AP' ('application_name')]; 
<bindtype> =['GR' ('group') | 'DB' ('database')] 
<hardness> = ['hard' | 'soft'] 
<command> = ['all' | 'gr' | 'db' | 'login' | 'app'] 

Usage


create and drop


add and remove

Permissions

By default, only the System Administrator or users with the SA role can execute sp_tempdb.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect





Copyright © 2005. Sybase Inc. All rights reserved. sp_thresholdaction

View this book as PDF