Recovering from a Non-Master Device offline Failure

Symptoms :

• 840 error on recovery
• Database marked as not recovered (status bit 64) and "suspect" (bit 256)
• Recovery for that database fails

Reasons :

• Adaptive server cannot activate a device during startup recovery of a database
• Possible cause :

1. Device offline , busy , damaged , removed
2. Network problems
3. file permission problem

Dignostics :

• check ASE  error log & OS error log to find type of device problem
• login to ASE server and check databse affected & its status "not recovered" or "suspect".

Cure :

• If the suspect device is still usable and data remain on device :-

1. Dignose and fix the device problem if necessary.
2. reset sysdatabases.status by manually turning off bit 256 , use below command
3.  sp_configure "allow update to system tables",1
4. go
5. begin tran
6. update sysdatabases set status = status &~ 256
7. where dbid =db_id('sales')
8. -----check sysdbabases
9. commit tran
10. sp_configure "allow update to system tables",0
11. go
12. sysdatabases.status bit 64(offline) will be reset automatically during recovery.

• If data device or log device are unusable , it is necessary to go to backups.