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 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.