You can create “Thresholds” to which are used to monitor space on a database segments. When free space on the segment falls below the specified level, Adaptive Server executes the associated stored procedure. You can also use thresholds to automate backup procedures.
Implementation of threshold in Sybase is a two-step process.
1) Create a stored procedure, which does the required action, for example to dump the database or the transaction log or just print the remaining pages left in the segment in the error log.
2) And create thresholds in a database to tell the ASE when to execute the above stored procedure.
Yeah, make sure its got "trun log on checkpoint", or even "abort tran on log full". Though the temp tables should be dropped automatically by the application.
As for thresholds - you can't always add them to model, since your model db is prob 2Mb but your tempdb could be many Gbs...
Here are my thoughts on tempdb space usage/monitoring
- Putting thresholds on a tempdb is not a good idea as the db option - "trunc. log on chkpt" is always there to trun the log.
- You do not need to explicitly set "trunc. log on chkpt" option for tempdb as it is always implicitly applied by ASE, regardless of whether you set this option or not.
- Remember, ASE truncates the the log upto the last open transaction, if you have an open transaction towards the end of your log then ASE will clear up the log ONLY upto that transaction, rest of the log(before that transaction) will NOT be cleared. (Sybase logs are written sequentially).
- In a production running server, you dont want tempdb to be fill upto 80%. You do not want simultaneous threads in ASE to compete for tempdb space. Aim for 50% max usage. You will be safe.
- Estimate your tempdb space requirements. Begin with application monitoring. Look for long running transactions. Check your batch jobs. There is an excellent information in Sybase admin guide (or performance tuning guide) for estimating tempdb size. Sorry I do not have the link at this moment.