Author:-

                  Sagar Akhare

                 Sybase DBA

                Clover Infotech, Mumbai

                3117sagar@gmail.com

----------------------------------------------------------------------------------------------------------

TEMPDB IN SYBASE

Introduction:-

Adaptive Server has a temporary database, tempdb, provides a storage area for temporary tables and other temporary working storage needs. The space in tempdb is shared among all users of all databases on the server.

The default size of tempdb depends on the logical page size for your server, 2, 4, 8, or 16K. Certain activities may make it necessary for you to increase the size of tempdb. The most common of these are:

·      Large temporary tables.

·      A lot of activity on temporary tables, which fills up the tempdb logs.

·      Large sorts or many simultaneous sorts. Subqueries and aggregates with group by also cause some activity in tempdb.

You can increase the size of tempdb with alter database. tempdb is initially created on the master device. Space can be added from the master device or from any other database device.

Adaptive Server allows you to create and manage multiple temporary databases in addition to the system temporary database, tempdb. Multiple temporary databases reduce contention on system catalogs and logs in tempdb.

There are two kinds of temporary tables:

·      Tables that can be shared among Adaptive Server sessions

Create a shareable temporary table by specifying tempdb as part of the table name in the create table statement. For example, the following statement creates a temporary table that can be shared among Adaptive Server sessions:

create table tempdb..authors

(au_id char(11))

drop table tempdb..authors

Adaptive Server does not change the names of temporary tables created this way. The table exists until the current session ends or until its owner drops it using drop table.

·      Tables that are accessible only by the current Adaptive Server session or procedure

Create a nonshareable temporary table by specifying a pound sign (#) before the table name in the create table statement. For example:

create table #authors

(au_id char (11))

The table exists until the current session or procedure ends, or until its owner drops it using drop table.

If you do not use the pound sign or “tempdb..” before the table name, and you are not currently using tempdb, the table is created as a permanent table. A permanent table stays in the database until it is explicitly dropped by its owner.

This statement creates a nonshareable temporary table:

create table #myjobs

(task char(30),

start datetime,

stop datetime,

notes varchar(200))

Syntax to create temporary database:-

create [temporary] database database_name

        [on {default | database_device} [= size]

                [, database_device [= size]]...]

        [log on database_device [= size]

                [, database_device [= size]]...]

        [with {override | default_location = "pathname"}]

        [for {load | proxy_update}]

                        Parameters

temporary

indicates that you are creating a temporary database.

Note:- all other parameters are same like other user databases.

sp_tempdb:-

Description

sp_tempdb allows users to:

·      Create the default temporary database group

·      Bind temporary databases to the default temporary database group

·      Bind users and applications to the default temporary database group or to specific temporary databases

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.

Moving tempdb off the master device:-

When you have increased tempdb on separate devices you can configure tempdb so that the master device is unused. This increases the performance of tempdb even further. There are various techniques for this, all with their pros and cons but I recommend the following. Modify sysusages so that segmap will be set to 0 for the master device. In other words, change the segments of tempdb so that the master device is unused. This can be done with the following statements:

sp_configure "allow updates to system tables",1

go

update master..sysusages

   set segmap = 0

   where dbid = 2

   and lstart = 0

go

sp_configure "allow updates to system tables",0

go

shutdown -- reboot now!

go 

When you use this configuration you should know the recovery procedure just in case one of the devices of tempdb gets corrupted or lost. Start your ASE in single user mode by adding the -m switch to the dataserver options. Then submit the following statements:

update master..sysusages

    set segmap = 7

    where dbid = 2

    and lstart = 0

go

delete master..sysusages

   where dbid = 2

   and lstart > 0

go

shutdown -- reboot now!

go

Remove the -m switch from the dataserver options and restart ASE. Your tempdb is now available with the default allocation on the master device.

Sizing tempdb :-

As a general estimate, the size of tempdb should be equal to the largest of three values below:-

1)      The total space necessary when running longest query

2)      10% of total data or 100MB, whichever is smaller

3)      1 to 2 MB per user connection.

Note:-  If a query fails because tempdb is too small, the error message will flash on the screen.

How management of tempdb affects performance:-

Good management of tempdb is critical to the overall performance of Adaptive Server. tempdb cannot be overlooked or left in a default state. It is the most dynamic database on many servers and should receive special attention.

If planned for in advance, most problems related to tempdb can be avoided. These are the kinds of things that can go wrong if tempdb is not sized or placed properly:

·      tempdb fills up frequently, generating error messages to users, who must then resubmit their queries when space becomes available.

·      Sorting is slow, and users do not understand why their queries have such uneven performance.

·      User queries are temporarily locked from creating temporary tables because of locks on system tables.

·      Heavy use of tempdb objects flushes other pages out of the data cache.

Installation of multiple tempdb:-

Use following syntax for creating or dropping by using available parameters in tempdb

User created Temporary databases:-

            * These db's are usually created to minimize resource contention(such as system catalog & log

            contention) in system tempdb.

            * User created tempdb are just like system tempdb in that they are,

1) used primarily to create temporary objects

2) Re-created rather than recover.

Syntax for creating  temp db group

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.

            NOTE:- The purpose of the 'create' option is to create the 'default' group if it doesn't already       exist, otherwise there's no (current) need for the 'create' option.

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:

·      login_name (or LG)

·      application_name (or AP)

Values are not case-sensitive.

objname

is the name of the object you bind or unbind.

bindtype

is the bind type. Valid values are:

·      group (or GR)

·      database (or DB)

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:

·      all or no argument – displays the default temporary database group, all database-to-group memberships, and all login and application bindings.

·      gr – displays the default temporary database group. sp_tempdb show displays all temporary databases bound to the default temporary database group whether you specify “default” for the name option or not.

·      db – displays all databases to group memberships. If you provide name, then only the database to group memberships for the database name are printed.

·      login – displays all login bindings where login is not NULL. If you provide name, then only the bindings for the login name are printed.

·      app – displays all bindings where the application is not NULL. If you provide name, then the bindings for the application name are printed.

 tempdb 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:

·      dbname – the name of a temporary database. If you provide a nontemporary database name for dbname, sp_tempdb who executes, but does not report any active sessions bound to it.

help

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

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']

* To show members of group

sp_tempdb show, gr , 'default'

* Show all tempdbs

sp_tempdb show

Allocations of tempdb:-

Use sp_helpdb to see the size and status of tempdb. The following example shows tempdb defaults at installation time:

Example:-

sp_helpdb tempdb

name      db_size  owner  dbid   created     status

--------- -------- ------ ------ ----------- --------------------

tempdb    2.0 MB   sa     2     May 22, 1999 select into/bulkcopy

device_frag  size    usage        free kbytes

------------ -------- ------------ ---------

master       2.0 MB  data and log 1248

User-created Temporary databases:-

·         Are like normal user database (status 3 in sysdatabases)

·         when created:-

  1) dboption “select into/bulkcopy” is set

  2) dboption “trunc log on chkpt” is set

  3) “guest” user is added

  4) “create table” permission is granted to public

Binding objects to caches:-

sp_bindcache assigns a database, table, index, text object or image object to a cache. Before you can bind an entity to a cache, the following conditions must be met:

·      The named cache must exist, and its status must be “Active.”

·      The database or database object must exist.

·      To bind tables, indexes, or objects, you must be using the database where they are stored.

·      To bind system tables, including the transaction log table syslogs, the database must be in single-user mode.

·      To bind a database, you must be using the master database.

·      To bind a database, user table, index, text object, or image object to a cache, the type of cache must be “Mixed.” Only the syslogs table can be bound to a cache of “Log Only” type.

·      You must own the object or be the Database Owner or the System Administrator.

Binding objects to caches is dynamic.

The syntax for binding objects to caches is:

sp_bindcache cache_name, dbname [,[owner.]tablename

[, indexname | "text only" ] ]

The owner name is optional if the table is owned by “dbo.”

This command binds the titles table to the pubs_cache:

sp_bindcache pubs_cache, pubs2, titles

To bind an index on titles, add the index name as the third parameter:

sp_bindcache pubs_cache, pubs2, titles, titleind

The owner name is not needed in the examples above because the objects in the pubs2 database are owned by “dbo.” To specify a table owned by any other user, add the owner name. You must enclose the parameter in quotation marks, since the period in the parameter is a special character:

sp_bindcache pubs_cache, pubs2, "fred.sales_east"

This command binds the transaction log, syslogs, to the pubs_log cache:

sp_bindcache pubs_log, pubs2, syslogs

The database must be in single-user mode before you can bind any system tables, including the transaction log, syslogs, to a cache. Use sp_dboption from master, and a use database command, and run checkpoint:

sp_dboption pubs2, single, true

text and image columns for a table are stored in a separate data structure in the database. To bind this object to a cache, add the “text-only” parameter:

sp_bindcache pubs_cache, pubs2, au_pix, "text only"

This command, executed from master, binds the tempdb database to a cache:

sp_bindcache tempdb_cache, tempdb

You can rebind objects without dropping existing bindings

Binding to groups and tempdb

The sp_tempdb. . . 'bind'...’unbind’ system procedure allows you to bind, or unbind, an application or login to specific temporary database or tempdb group. After you create the binding, when the application or login connects to the server, Adaptive Server assigns the specified temporary database or temporary database group to which it is bound. Binding allows you to control the temporary database assignments for specific applications or logins.

This example binds the log in sa to the default group:

sp_tempdb 'bind', 'lg', 'sa', 'GR', 'default'

This example unbinds the login sa:

sp_tempdb 'unbind', 'lg', 'sa'

Binding user-created temporary databases to a data cache

Use sp_bindcache to bind a database to a data cache. Binding a user-created temporary database is similar to binding any other user database, except in the following:

A user-created temporary database is considered to be in use as long as a session is assigned to it, even if no activity is actually taking place on behalf of the active session. However, to change the cache binding of the database, the database must be exclusively locked. You cannot lock the database as long as there is an active session assigned to it. The steps to overcome this are similar to those necessary to drop a database:

Changing the cache binding of a database

1.      Use sp_tempdb to remove all bindings involving the temporary database, including any database-to-default-group bindings as well as any application and login bindings to the database.

2.      Either wait for any active sessions already assigned to the temporary database to drain out, or terminate these sessions if required. Use sp_tempdb to list the active sessions that are assigned to the database.

3.      Proceed with the database to cache binding.

4.      Restore any bindings removed in the first step.

How to create a temporary db & bind user to it:-

Create the tempdb that will be used by the login:

USE master

go

create temporary database tempdb_stats  on tempdb_stats = 4000

go

USE tempdb_stats

go

- Create the login that will use the tempdb

exec  sp_addlogin  'userstats',

 'xxxxxxxx',

 @defdb='master',

 @deflanguage='us_english'

go

exec  sp_locklogin  'userstats', 'unlock'

go

- Let’s bind the login to the tempdb:

sp_tempdb 'bind', 'lg',  'userstats',  'DB',  'tempdb_stats'

go

Only new sessions are affected by the new binding.

Bindings can be hard or soft:

·      Soft bindings (default)- logins never fail despite any failures to assign a temporary database to the session according to the binding in effect. When all else fails, a session should always end up being assigned to the system tempdb.

·      Hard bindings - if an assignment of a temporary database to a session cannot be made according to the binding in effect, the login fails.

Session binding

At login time, a session is assigned to a temporary database, which remains in effect for the duration of that session and cannot change. Bindings are read from sysattributes, and are chosen according to these parameters:

·      If binding of type LG (login) exists, use that binding.

·      If binding of type AP (application name)exists, use that binding.

·      Bind session to a temporary database within the default group.

Binding with Temporary Database

·         When you successfully execute sp_tempdb bind, it insert a new entry to sysattributes table to represent this binding.

·         Only new session are affected by the new binding.

·         A login or application can be bound on separate tempdb for maintenance & disaster recovery.

·         Be careful when binding dba logins to specific tempdb, it will effect maintenance or batch processes running under this logins.

@@tempdbid :- stores the session assigned temporary database

ex:-   select db_name (@@tempdbid)

will return session assigned temporary database

Some common Problems & solutions related with tempdb

Prevention of a full logsegment

One of the database options that can be set with the sp_dboption stored procedure can be used to prevent this. When you do:

sp_dboption tempdb,"abort tran on log full",true

the transaction that fills up the transaction log in tempdb is automatically aborted by the server.

Prevention of a full segment for data

Default or system segments are full

The default or system segments in tempdb, where the actual data is stored, can also get full, just like any ordinary database. Your query is cancelled with a Msg 1105: Can't allocate space for object '#a_____00000180017895422' in database 'tempdb' because 'default' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase size of the segment. This message can be caused by a query that creates a large table in tempdb, or an internal worktable created by ASE used for sorts, etc. Potentially, this problem is much worse than a full transaction log since the transaction is cancelled. A full log segment leads to "sleeping" processes until the problem is resolved. However, a full data segment leads to aborted transactions.

Prevention of a full segment for data:-

The Resource Governor in ASE allows you to deal with these circumstances. You can specify just how much space a session is allowed to consume within tempdb. When the space usage exceeds the specified limit the session is given a warning or is killed. Before using this feature you must configure ASE (with sp_configure)to use the Resource Governor:

sp_configure "allow resource limits",1

After a reboot of the server (12.5.1. too) you can use limits: (sp_add_resource_limit)

sp_add_resource_limit "petersap",null,"at all times","tempdb_space",200

This limit means that the user petersap is allowed to use 200 pages within tempdb. When the limit is exceeded the session receives an error message (Msg 11056) and the query is aborted. Different options for sp_add_resource_limit make it possible to kill the session when the limit is exceeded. Just how much pages a user should be allowed to use in tempdb depends on your environment. Things like the size of tempdb, the number of concurrent users and the type of queries should be taken into account when setting the resource limit. When a resource limit for tempdb is crossed it is logged into the Sybase errorlog. This makes it possible to trace how often a limit is exceeded and by who. With this information the resource limit can be tuned. When you use multiple temporary databases the limit is enforced on all of these.

How to move tempdb off from master device

1.      alter database tempdb on tempdev = 20

2.      sp_dropsegment "default", tempdb, master

3.      sp_dropdegment system, tempdb, master

4.      sp_dropdegment logsegment, tempdb, master

5.      To verify that the default segment no longer includes the master device, issue this command:

select dbid, name, segmap
from sysusages, sysdevices
where sysdevices.low<= sysusages.size + vstart
and sysdevices.high>= sysusages.size + vstart -1
and dbid = 2
and (status = 2 or status = 3)

The segmap column should report "1" for any allocations on the master device, indicating that only the system segment still uses the device: dbid name segmap

1.      Alter tempdb onto another device, if you have not already done so. For example:

2.   alter database tempdb on tune3 = 20

3.      Issue a use tempdb command, and then drop the master device from the segments:

4.   sp_dropsegment "default", tempdb, master

5.   sp_dropsegment "system", tempdb, master

6.   sp_dropsegment "logsegment", tempdb, master

7.      To verify the segments no longer include the master device, issue this command against the master database:

8.   select dbid, name, segmap

9.   from sysusages, sysdevices

10. where sysdevices.vdevno= sysusages.vdevno

11. and dbid = 2

12. and (status&2=2 or status&3=3))

The segmap column should report “0” for any allocations on the master device, indicating that no segment allocations exist:

 dbid   name            segmap     

 ------ --------------- -----------

      2          master           0

      2           tune3           7

Alternatively, issue:

use tempdb

sp_helpdb 'tempdb'

device_fragments     size      usage             created   free kbytes

-----------------   ------   ----------  -----------------  ----------

master              4.0 MB   data only     Feb 7 2008 2:18AM      2376

tune3               20.0 MB  data and log May 16 2008 1:55PM     16212

device    segment

--------- -----------------------------

master    -- unused by any segments --

tune3                         default

tune3                      logsegment

tune3                           system