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.
Certain activities may make it necessary for you to increase the size of tempdb:
*Large temporary tables.
*A lot of activity on temporary tables, which fills up the tempdb logs.
*Large or many simultaneous sorts. Subqueries and aggregates with group by also cause some tempdb activity.
Use alter database to increase the size of tempdb. tempdb is initially created on the master device. You can add space to tempdb from the master device or from any other database device.
If you run update index statistics against large tables, the command fails with error number 1105 if tempdb is not large enough.
You can 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.
https://infosybase.blogspot.com/2020/07/how-to-create-additional-tempdb-in.html
How to find which spid is filling tempdb ?
Make sure you have mon tables activated,Once verified you can issue the following select statement:
>select SPID, DBName, ObjectName, PartitionSize from master..monProcessObject where DBID = tempdb_id(SPID) order by SPID
>go
Actions to be taken when tempdb is full:
1.Check error log
cd $SYBASE/ASE*/install/
cat ASETST.log | grep "tempdb"
2.Try login to the system using isql
If you are not able to login ,only option is to kill the sybase instance in OS level.
3.Find out the space free in tempdb using the below command
>select lct_admin(‘logsegment_freepages’dbid)
>go
4.try running dum tran with truncate only
>dump tran tempdb with truncate_only
>go
if still not releasing the space then try the below command
5. try running dump tran with no_log
>dump tran tempdb with no_log
>go
6.Find process from syslogshold/sysprocesses and check the tempdb pages
>select *from syslogsholds
>go
>select pssinfo(<spid no>,<tempdb_pages>)
>go
inform application team about condition ask for killing culprit SPID if he says yes then
7.kill the process
>kill <spid no>
>go
If user says no than alter the tempdb
use the disk init command to initialize the disk and then alter the tempdb
>alter database tempdb on <device_name>='2G'
>go
7. you can also use lct_admin(‘abort’,0,2) to kill all suspended transactions.
8.If still not able to clear the space ,take the final approval and restart the server.
Recommendation :
Ask application team to check the query while running the large batches and ask them to tune the query, use frequent commits and also make sure to run the jobs in small batches.
https://infosybase.blogspot.com/2020/07/best-practices-for-shutdown-sap-ase.html
https://infosybase.blogspot.com/2020/07/difference-between-shutdown-and.html