Troubleshoot if tempdb gets filled /Proactive approach for tempdb

Proactive approach for tempdb works good / processing  work well

sp_configure a maximum buffers per lava opeator sets an upper limit for the number of buffers used by Lava operators that perform sorting or hashing (which are “expensive” in terms of processing). Lava operators use buffers from the session’s tempdb data cache pool as a work area for processing rows . Some queries require less I/O if you increase max buffers per lava operator.
number of sort buffers specifies the amount of memory allocated for buffers used to hold pages read from input tables and perform index merges during sorts. number of sort buffers is used only for parallel sorting.
Adaptive Server allocates sort buffers from the cache to which the table is bound. If the cache is not bound to a table, Adaptive Server allocates the sort buffers from the cache to which the database is bound. If the cache is not bound to a table or a database, Adaptive Server allocates sort buffers from the default data cache.
Parallel sorts are used when you:
Run updates statistics  
Create indexes

----------------------------------------------------------------------------

Try to find out the Active process that is filling up the temp db space.

1)  run monProcess  for getting the spid   that are currently executing or waiting.

2) select lct_admin (abort , spid ) ..... this spid , will get from above monProcess output.

3) select spid , command , pssinfo(14,'tempdb_pages')   from monProcess order by desc.




1) If the transaction log of tempdb is full then you can login through sa and type following command.

1> dump tran tempdb with truncate_only
2> go

2) If the database is full then you can increase the size of the database on a free device.

1> alter database tempdb on device_name = size
2> go


3) use following command It will abort all open transactions.

But be sure the task by confirming with the concern users.

1>select lct_admin(0,2)
2>go

Restarting the server is not recommanded.
lct_admin (0,2) would abort all open transactions, or you can go for altering the tempdb space. Multiple tempdb's is a feature which can be implemented to minimize such issues of tempdb getting full.


Temp db full

By using tempdb's

Since ASE 12.5 we can create a user defined tempdb (using sp_tempdb) and bind an admin login
to this new tempdb. This admin account can then run sp_who to find out who filled tempdb and kill the process.

Sometimes the kill won't work The new 'multiple tempdb' feature in 12.5.0.3 allows you to avoid the problem that tempdb is full and cannot access the fake tables in master.
In pre-12.5.0.3, we can get some information from different sources, like dbcc pss, dbcc lock and dbcc log

Follow these steps to solve tempdb full

(i) use 'tempdb_space' resource limits (in 12.5+) and

(ii) if your tempdb gets full anyway,use lct_admin to abort the transactions causing this.
Instead of this it's a good idea to

(iii) enable the 'abort tran on log full' option for tempdb so that LOG SUSPEND statuses will not remain until the DBA takes action.

orphan tables in tempdb

If an spid that owns the table no longer exists in sysprocesses.
This happens sometimes when a killed spid isn't cleaned up properly,
leaving temp tables behind. These are called "orphaned" temp tables.

Running

dbcc orphantables

will list orphaned tables in tempdb .

dbcc orphantables ("drop")

will try to clean them up.


suggestions to help avoid running out of tempdb space frequently :-  1- If you run of of tempdb space frequently (even just 1105 errors) you should consider increasing the size of the tempdb.

2- Use the resource governor to limit the amount of tempdb particular applications or logins can use.
Enable this by sp_configure "allow resource limits",1 and See information on sp_add_resource_limit in the System Administration Guide at:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31644.1502/html/sag2/sag230.htm
and
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31644.1502/html/sag2/X38118.htm
and
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1502/html/sprocs/X89837.htm

3- Add more thresholds to your tempdb database. This way the thresholdaction procedure may be able to avert the tempdb filling with earlier warning.
Note: since the nature of any tempdb database is that it is refreshed when ASE reboots, the thresholds would need to be set up each time ASE is rebooted.

https://sites.google.com/site/dbatipsandtricks/querry/whousethespaceintempdbs
---> querry on pssinfo()