I had a problem in one of my user databases.
(I am using sybase 188.8.131.52 on NT)
When I looked at logsegment for my userdb, its was getting full though truncate log on checkpoint is turned on. When I saw syslogshold table, I could see one topen transaction in the database and corresponding process was in sleeping mode. So when I tried to kill that process, I wasn't able to do so b'coz process was sleeping. I tried to reboot the sybase server, but then database cudn't recover as I got following message in errorlog:
00:00000:00001:2004/05/24 01:10:44.96 server Recovering database 'myDB'.
00:00000:00001:2004/05/24 01:10:44.99 server Redo pass of recovery has processed 42 committed and 0 aborted transactions.
00:00000:00001:2004/05/24 01:10:45.02 server Error: 1105, Severity: 17, State: 3
00:00000:00001:2004/05/24 01:10:45.02 server Can't allocate space for object 'syslogs' in database 'myDB' because 'logsegment' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase size of the segment.
00:00000:00001:2004/05/24 01:10:45.02 server Error: 3475, Severity: 21, State: 7
00:00000:00001:2004/05/24 01:10:45.02 server There is no space available in SYSLOGS for process 1 to log a record for which space has been reserved. This process will retry at intervals of one minute. The internal error number is -4.
I cudn't increase the size of logsegment since database was in recovery mode.Then I have to mark database as suspect, drop it and recreate it as it was one of the smallest database I have.
The resolution I did, was not perfect so I would like to know if I could have followed a simpler approach than this, like killing spid which is responsible for all this mess and how can I find root cause of this open transaction (definately there is no major updates on sundays)
if a process is in log suspend state you can abort the process by lct_admin. If that approach do not succeed you have to recycle the server after marking the affected database as suspect. After that you need to run the dump tran command to free up the log..
I did the same thing as you mentioned.
I cudn't abort the process using lct_admin as process was sleeping. Then I have to mark the database suspect, recycle sybase server, and then since my userdb was very very small, and I had an upto date dump, so I dropped the db and recreated that as I was not sure whether It will be helpful to change the db status from suspect to normal.(which should work when I gave it a after thought). Also we cant take tran dump unless db status is changed to normal from suspect.