When tempdb is full, you really can't do anything, even for DBA. The only solution at the moment is either expand tempdb or do a server recycle. Once the server is recycled, all the traces are gone. There is no way to find out who/what caused the trouble.
The key here is to capture the related information before tempdb is full. Use threshold action procedure to capture process info in sysprocesses, syslogshold, syslocks and other related info, save it in permanent tables. Even after the server is recycled, the info is still there to be analyzed. But remember thresholdaction definition will be gone after server recycle. So a tip I am using is to add the create threshold statement into server RUN file.
There are other options to use, ie. Resource limit, multiple tempdb etc.
Blog Archive
-
►
2007
(49)
-
►
February
(34)
- what if logsegment is full because of replication
- How to see the sql running on ASE
- What to do when Sybase stable queue filled up?
- SAIC - California
- Sybase backup performance
- Sybase 15 upgrade over last weekend
- What is SOX compliance
- Holiday
- Sybase row level locking
- setfile - File access control command
- How to drop Sybase xp_cmdshell
- Unix ENV variable - save some typing work
- Copy directory to another machine using scp
- How to delete file named "-l" on Unix box
- What's next available device number?
-
►
February
(34)
4 comments:
Actually you can address this now - since ASE 12.5 you 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. Of course sometimes the kill won't work .. or the rollback takes too long. But at least you can point the finger.....
Your suggestion for adding the thresholds via a RUN_ file is a good one :) A point I would add though is to make the threshold quite large ... when something goes wrong in tempdb it tends to fill quickly, and if you only start recording data at 90% full, you'll find your threshold proc is suspended before it finishes!
That's a good one using dba tempdb. thanks for sharing.
Please show me How you add a threshold to tempdb in Run File, thanks
Post a Comment