I have a Sybase ASE server that hangs every week or so, indicating tempdb log segment is full.
I have tried everything. trunc log on chkpt is enabled and it works correctly resetting used_pages about every 60 seconds or so.
The problem is, not all the pages freed are returned to free_pages. So, over time, free_pages eventually ends up at 0, while used_pages is minimal. The values I'm referring to come from the query sp_spaceused syslogs on tempdb. It's like a memory leak!
Currently when I run this command I get:
total_pages: 64000
free_pages: 29719
used_pages: 251
reserved_pages: 0
Every time I run the command, used_pages increases which is also odd.
This database is running on 64-bit Windows Server 2003. I have another similarly configured ASE server that does not have these issues. The contents of this other database are similar. This database is running on 32-bit Windows Server 2003. There's no need to move tempdb to a different device or expand its size any further because this other server operates perfectly and it is configured the same as the one that has odd behavior.
It depends on application that running on this ASE. Try to monitor application with ASE monitoring tables.
Look at very advanced presentation http://download.sybase.com/presentation/TW2005/ASE115.pdf.