Skip to Content
Author's profile photo Former Member

Why and how to create an additional tempdb for Sybase ASE

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
disk init name ='tempdb2dev' ,
physname='/sybase/SM1/sybsystem/tempdb2dev.dat', size='60G'
create temporary database tempdb2 on tempdb2dev='60G'

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'
sp_tempdb 'bind','lg','SAPSR3','db','tempdb2'

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

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Eduardo Rezende
      Eduardo Rezende

      Beate now with good examples/blogs/KBAs in Sybase area!
      Good to know. šŸ™‚

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Former Member
      Former Member
      Blog 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.

      Author's profile photo Stephan RoƟmanith
      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

      Author's profile photo Former Member
      Former Member
      Blog 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.



      Author's profile photo Former Member
      Former Member

      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'



      Author's profile photo SAP BASIS

      Sybase definetely needs more tutorials like this

      Author's profile photo Pedro Gonzalez SantibaƱez
      Pedro Gonzalez SantibaƱez

      thanks work fine for me!