1.Check error log to find the tempdb full.
2.Find out the space free in tempdb using
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