1. Check error log to find the tempdb full.
2. Find out the space free in tempdb using
select lct_admin(‘logsegment_freepages’dbid)3. dum tran with truncate only if still full than..
4. dump tran with no_log still full than..
5. Find process from syslogshold/sysprocesses.
Inform user about condition ask for killing if he says yes then
6. Kill the process
If user says no than alter the tempdb
7. You can also use lct_admin(‘abort’,0,2) to kill all suspended transactions or last on recomendation restart the server
How to find which sid is filling tempdb ?
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