Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Msg 840, Level 17, State 2:

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

Tags:
Former Member replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question