Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
1.Check error log to find the tempdb full.
2.Find out the space free in tempdb using
1 | 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:
1 | select SPID, DBName, ObjectName, PartitionSize from master..monProcessObject where DBID = tempdb_id(SPID) order by SPID |
If we have log shipping to disaster/remote site DB, and after “dump tran with no_log”.
What should we do here for remote site (replicated) DB here?