Unanswered: Issues while bringing database online after load
I was loading my database from a production copy and got the below messages during recovery. The database has not come online and the logsegment is still full. Can you help me recover the db?
Started estimating recovery log boundaries for database 'yantra2'.
Database 'yantra2', checkpoint=(43119574, 18), first=(43119574, 18), last=(43123520, 5).
Completed estimating recovery log boundaries for database 'yantra2'.
Started ANALYSIS pass for database 'yantra2'.
Completed ANALYSIS pass for database 'yantra2'.
Recovery of database 'yantra2' will undo incomplete nested top actions.
Hit the last chance threshold on database 'yantra2' for log segment during upgrade. Not spawning threshold action procedure.
Msg 1105, Level 17, State 5:
Server 'SYBTEST1', Line 1:
Can't allocate space for object 'syslogs' in database 'yantra2' 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.
Msg 12535, Level 18, State 1:
Server 'SYBTEST1', Line 1:
Post-processing for system catalog 'sysreferences' could not be completed during online of database 'yantra2'.
SQL Server could not bring database 'yantra2' online.
seems there was a big tran was running on the source database when you had taken the dump, because of which during the undo phase of log recocery its filling up the log and database is not commng online.
Here you can proceed as below.
1. Drop and recreate the database on target and icrease its log and then load again
2. Or checkpoint the database on source and take a fresh dump and then load it to targtet server.
Notice that there are number of factors that are abstructing you to perform that operation. The notable one is that of the logsegment space. The logsegment of that database must have got full or must have crossed a threshold value hence you are getting that error.
Altering the log segment of that database should solve the problem. (recomended)
If you cannot alter the logsegment space and if you are not using an OLTP server, then use,
dump tran <db_name> with truncate only
If you are using a server which supports OLTP then dump the transaction log to some device and then proceed.
Error: 1105, Severity: 17, State: 3
Can't allocate space for object 'syslogs' in database 'XYZ' 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.
If above error came during the database recovery (after load during online database or after database reboot). Follow the below steps to make database online
1. Update status to -32768 for the database in sysdatabases.
2. reboot the ASE server.
3. dump tran <database> with no_log
4. reset the status for the database as previous and restart the database.