Skip to Content

As most RDBMS Sybase ASE also uses the concept of a temporary database which is used to handle intermediate results, e.g. for sorts, worktables and so on.

A default installation of Sybase ASE will always have a small tempdb (normally located on the master device) – still, for the workload of most applications a much larger temporary database will be required.

To avoid that you run into problems like SQL Error 1105, it’s a good idea to create and additional tempdb with a size that is able to serve your system’s requirements. As a rule of thumb for the size use:

(square root ( size of <SID> DB in Gigabytes)) multiplied by 2

If your <SID> database has a size of 900 GB, your tempdb should be sized to ~ 60 GB. This is not a strict rule but a rule of thumb to start with a useful value. If you discover that it’s still insufficient, you can of course tune it lateron.

Creating an additional tempdb

To create an additional tempdb, you can use the command line tool isql and adjust the below example:

use master
go
disk init name ='tempdb2dev' ,
physname='/sybase/SM1/sybsystem/tempdb2dev.dat', size='60G'
go
create temporary database tempdb2 on tempdb2dev='60G'
go

Note – as long as you don’t bind any users to this newly created tempdb, nothing will change so you now need to bind the sapsa and the SAPSR3 to this new tempdb to ensure that it’s going to be used.

Binding users to the newly created tempdb

To bind your logins to the tempdb you can use sp_tempdb. A detailed documentation is available under sp_tempdb but the following commands should already be sufficient for this purpose (the name provided in the last parameter of course needs to match the name you used in the create temporary database statement 😉 )

sp_tempdb 'bind','lg','sapsa','db','tempdb2'
go
sp_tempdb 'bind','lg','SAPSR3','db','tempdb2'
go

You should also have a look at SAP KBA 1752266 which always has the most current information regarding this.

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Ulrich Denneler

    I recently have increased the first temporary database (usually called “tempdb”), using an additional device.

    Is there any reason why it’s better to create a second temporary database, rather than increasing the first one?

    (0) 
    1. Beate Grötschnig Post author

      Increasing the default tempdb will serve the purpose as well… but …

      The main reason why it’s a good idea to explicitly create a second tempdb is that you can explicitly assign the sapsa and sapsr3 logins to the newly created tempdb.

      All other logins will still use the first tempdb which is created by default during the installation – and the default Group will remain assigned to the first tempdb.

      This way you ensure that the newly created tempdb is properly sized for the needs of your R/3 system and is used by your R/3 system.

      (0) 
      1. Stephan Roßmanith

        Hello Beate,

        if you bind the users sapsa and SAPSR3 to the new tempdb with the command

             “sp_tempdb ‘bind'”

        do this two user only use the new tempdb or do they use both, the new and the old tempdb?

        regards, Stephan

        (0) 
        1. Beate Grötschnig Post author

          Hi Stephan,

          correct – if you bind the user to a specific tempdb, all transactions from this user will use the tempdb you specified only and no other tempdb.

          Regards,

          Beate

          (0) 
  2. syb anva

    One point I like to add : As tempdb  contains temporary tables and recovery of this database is not a concern, always set dsync /direct io setting false for all tempdb devices to increase the performance of tempdb:

    Like as

    1. disk init name =‘tempdb2dev’
    2. physname=‘/sybase/SM1/sybsystem/tempdb2dev.dat’, size=’60G’ ,  dysnc=’false’

    It will improve performance for  tempdb. You can verify this setting using

    sp_helpdevice ‘device name’

    go

    :

    (0) 

Leave a Reply