Why tempdb :
Adaptive Server has a temporary database, tempdb, that
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
tempdb created on the master device during the installation of SAP ASE is too
small to store the transient objects needed for processing of SQL commands.
If you run update index statistics against large tables, the
command fails with error number 1105 if tempdb is not large enough, it’s a good
idea to create and additional tempdb with a size that is able to serve your
system’s requirements
Rather than
extending the default tempdb we recommend to create an additional tempdb to avoid
performance concerns that result from using a single temporary database, and to
create it on a separate device.
The following formula provides
a good starting point:
square root (total DB size in GB) * 2
Example : a
<SID> database expected size of 5TB will need sqrt(5120)*2=71.55*2=143GB .
Why multiple
temporary databases?
• Reduce
contention on system catalogs and log files in the system tempdb
• Can be
created on fast access devices
• Can be
created or dropped as needed.
Additional tempdb
configuration:
1.Log on to the
database using command:
isql -S<SID>
2. Switch to the
master database:
use master
go
3. Create and
configure the additional tempdb by using the following SQL commands template:
disk init name=saptempdbdev'
go
create
temporary database saptempdb on saptempdbdev
go
sp_dboption
saptempdb, 'ddl in tran', true
go
sp_dboption saptempdb, 'allow nulls by
default', true
go
sp_dboption
saptempdb, 'abort tran on log full', true
go
sp_dboption
saptempdb, 'allow wide dol rows', true
go
always set dsync /direct io setting false for
all tempdb devices to increase the performance of tempdb
4.Bind the
SAPSR3 login to the new temporary database only. If your system is Java-based
or dualstack bind the SAPSR3DB login to the new temporary databases.
Note: If you had run the sp_tempdb 'bind' command without 'hard' following an earlier version of this article we recommend to run sp_tempdb again to change to 'hard' binding for the respective logins.
For ABAP stack:
sp_tempdb
'bind','lg','SAPSR3','db','saptempdb', null, 'hard'
go
For Java stack
or dual-stack:
sp_tempdb
'bind','lg','SAPSR3DB','db','saptempdb', null, 'hard'
go
5. Bind the
sapsa login to both tempdb and saptempdb
sp_tempdb
'bind','lg','sapsa','gr','default'
go
temporary
databases are always recreated when the database is restarted, an additional
user in a tempdb will disappear after the next restart. The user must be added
to the 'model' database, so that it exists in the tempdb even after a restart.
use model
go
sp_adduser
SAPSR3
go
sp_adduser
SAPSR3DB
go
After the next
restart of the database, the user also exists in the tempdb