We have an ASE15.7 server that has a 4.5 Gb tempdb database. Typically, during the day, tempdb doesn't exceed more than 10% full. During our nightly batch we see peak usage at about 30%. For the last two days, mid morning, we've had tempdb fill-up and essentially shutdown the server since tempdb is not available. We've since turned on database option "abort tran on log full" for tempdb to minimize the impact on other users if this occurs again. What I'm struggling with is how to determine which user is responsible for all the tempdb space being consumed (assuming it's only one user). We plan to put a threshold on both data and log segments at 50% to alert us when tempdb usage is well above normal, to hopefully provide us some time to investigate before the "abort tran on log full" option kills the user.
The dynamic nature of tempdb makes it difficult to troubleshoot afterwards as most objects get deleted when transactions complete. Also, the fact that 'guest' owns most of the objects also complicates connecting tables to users.
We do plan to run a number of queries to gather information the next time it happens. Things like:
1 - sp_who
2 - sp_lock
3 - select * from master..syslogshold where dbid = 2 (although this pointed us in the wrong direction today when we ran it)
4 - select name from tempdb..sysobjects where type = "U" (and possibly join to systabstats to get a row count)
Is there some way, under the time constraint of tempdb eventually filling up and aborting the offending transaction, of determining the size of each table in tempdb? Our expectation is that one (or a few) tables will be using up most of the 4.5 Gb.
Indeed difficult, even a bad query with bad join criteria or bad grouping can fill tempdb and will not show as objects in tempdb
If it is a bad query then tempdb will fill pretty quick; "abort tran on log full" is a good choice.
You might have to monitor sql (select from monProcessSQLText or monSysSQLText)