More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
On startup, the transaction log of a database has filled and recovery has suspended, what can I do?
You might find the following in the error log:
00:00000:00001:2000/01/04 07:43:42.68 server Can't allocate space for object 'syslogs' in database 'DBbad' 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:2000/01/04 07:43:42.68 server Error: 3475, Severity: 21, State: 7
00:00000:00001:2000/01/04 07:43:42.68 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.
which can prevent ASE from starting properly. A neat solution from Sean Kiely (sean.kiely@sybase.com) of Sybase Technical Support, that works if the database has any "data only" segments. Obviously this method does not apply to the master database. The Sybase Trouble Shooting Guide has very good coverage of recovering the master database.
1. You will have to bring the server up with trace flag 3608 to prevent the recovery of the user databases.
2. sp_configure "allow updates",1
go
3. Write down the segmap entries from the sysusages table for the toasted database.
4. update sysusages
set segmap = 7
where dbid = db_id("my_toasted_db")
and segmap = 3
5. select status - 320
from sysdatabases
where dbid = db_id("my_toasted_db") -- save this value.
go
begin transaction
update sysdatabases set status = -32768 where dbid = db_id("my_toasted_db")
go -- if all is OK, then...
commit transaction
go
shutdown
go
6. Restart the server without the trace flag. With luck it should now have enough space to recover. If it doesn't, you are in deeper trouble than before, you do have a good, recent backup don't you?
7. dump database my_toasted_db with truncate_only
go
8. Reset the segmap entries in sysusages to be those as saved in 3. above.
9. Shutdown ASE and restart. (The traceflag should have gone at step 6., but ensure that it is not there!)
(Continued on next question...)
