How to know spid which is filling tempdb from temp table name in sybase

Question

I received alert that tempdb is going to be full and temp table name aslo provided. Now I want to know how to check which spid is filling tempdb from temp tablename.

Answer 1

The best is to use the montables. 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

for more information please see the official Sybase docs: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00967.1550/html/MigrationGuide/MigrationGuide14.htm

Answer 2

Note that the above query against monProcessObject doesn't detect 'implicit' tempdb usage. Ie., usage from an 'order by', 'group by' or other internal/hidden tempdb based worktables used by the query optimizer operations.

To monitor tempdb usage for a for implicit and explicit usage, try:

select spid,tempdb_pages=pssinfo(spid,"tempdb_pages")
from master..sysprocesses

This pssinfo function is available at least in version 15.5 and higher. Not sure about 12.5.4