On one of our test server, we had an abrupt reboot while ASE was rolling back a huge transaction. After restart, Sybase recovered that database and then did a dbccdb recovery as well. During the ANALYSIS and REDO passes went fine but during the UNDO pass, the transaction log got full:
Can’t allocate space for object ‘syslogs’ in database ‘dbccdb’ 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 to increase the size of the segment.
Dumping the transaction log did not work since the database was recovering and couldn’t be accessed. So we had to use a more hardcore way known as log suicide i.e. short-circuiting the recovery. This is generally not recommended and I wouldn’t have done it on our database but on dbccdb, it was worth a try.
First you need to allow updates on system tables as we need to manipulate the sysdatabases table:
use master go sp_configure "allow updates",1 go
The we’ll update the status of dbccdb:
update sysdatabases set status=-32768, status2=0 where name='dbccdb'
status=-32768 means bypass recovery mode.
status2=0 clears the info about the currently running recovery.
Then shutdown ASE:
shutdown with nowait
Now you can manually start the dataserver. When the dataserver is up again, execute the following to truncate the transaction log of dbccdb:
dump tran dbccdb with no_log
And set back the status of dbccdb to the default value:
update sysdatabases set status=4, status2=-32768 where name='dbccdb'
status=4 means that select into/bulkcopy are allowed.
status2=-32768 means that dbccdb has some portion of the log which is not on a log-only device. if it is not the case for you, use 0 instead.
2 thoughts on “Sybase: Transaction log full during recovery of dbccdb”
Saved me! This works 🙂 Thanks for posting!