Skip to Content

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 29, 2015 at 07:21 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 27, 2015 at 11:43 AM

    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

    Add comment
    10|10000 characters needed characters exceeded