Msg 840, Level 17, State 2:

[Johan_sapbasis]

Hi all,

I am in a bit of a tizzy.

I have DB where I have a relatively older backup SID.dmp available.

The problem is somehow or someone deleted the SID_log_002.dat device. With startup I see the following option:

Msg 840, Level 17, State 2:

Server 'SID', Line 1:

Device 'SID_log_002' (with physical name

'L:\sybase\SID\saplog_2\SID_log_002.dat', and virtual device number 14) has not

been correctly activated at startup time.  Please contact a user with System

Administrator (SA) role.

Also on this system enforce dump transaction sequence is set. Any idea how I can get out of this.

Also because of this DB is marked suspect.

Cannot checkpoint database 'SID' because its status is: not usable

Regards,

Johan

[former_member198560 · Accepted solution]

Hi Nicolaas,

You need to drop & recreate the problematic database and the devices and then restore from backup.

To drop a database that is unable to recover either because of corruption, or another unidentified reason, you must reset the status to suspect mode to use the dbrepair command.

Please check the below steps :

1. check the current status of the database and keep these results:

Log in via isql using sa user  :

use master

go

select name,status from sysdatabases where name = '<dbname>'

go

2.

sp_configure "allow updates",1

go

begin tran

go

update sysdatabases set status = 320 where name = <dbname>

go

commit (if one row updated Or rollback)

go

3. select name,status from sysdatabases

go

*** Verify that the only db with 320 status is your problematic db.

4. Shutdown and then Restart ASE server.

5. when the server is back up, do:

dbcc dbrepair(dbname, dropdb)

go

6. select name from sysdatabases

go

7. Drop the problematic device .

sp_dropdevice <devicename>

go

Now recreate the db and restore from backup.

8. Create the required device using disk init.

9. create the database using create database.

10. Restore database from backup using load database.

Hope this will be helpful.

~Gaurav

[Johan_sapbasis · Accepted solution]

Hi Gaurav,

Your answer was helpful but the correct steps in the correct sequence is:

SyBooks Online (Archive) 

The big difference being that you need to set the allows updated back to 0

Reset the allow updates option of sp_configure:

  1> sp_configure "allow updates", 0   2> go

Before you drop the database.

Johan

[kimon_moschandreou]

Hello,

If this old backup is good enough for you you have to recreate the database and restore it. In order to drop the database use

dbcc dbrepair(database_name,dropdb)

and then recreate it, if you have not a script please run

sp_helpdb db_name

before dropping and post the result, I will send you back the create database command. After recreating the database you may restore your backup

If it is possible to find the missing file (from a system backup, maybe?) you may stop ASE, put the file in place and restart. It is not guaranteed that the database will finish automatic recovery successfully, maybe something extra will be required.

Probably what you have in mind is none of the above, but somehow truncate all the transaction log and recreate it, you may check this

http://www.petersap.nl/SybaseWiki/index.php?title=Corruption_of_syslogs_(dbcc_rebuild_log)

I am not sure if this may work in your case

Best regards,

Kimon