Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    1

    Question Unanswered: Help removing a database that is on a device that does not exist.

    Hi,

    Here is my problem:

    We have a database (with no data) that is on a device for which there is no disk.

    If I try to drop the database, I get the following error:
    Number (840) Severity(17) State(1) Device 'xyz' (with physical name '/dev/rdsk/c1t1d0s0', and virtual device number 15) has not been correctly activated at startup. Please contact a user with SA role..

    I can understand this since we don't have a disk for this device afterall.

    If I try to dbcc DBREPAIR, I am told the following:
    Number (2573) Severity (16) State (1) Database 'xyz' is not marked suspect. You cannot drop it with DBCC.



    If I try to drop the device, I am told I cannot drop it since I have a database that is using it.

    So how do I get rid of this database and device from my system?

    Thank you for any help!

    Brandon

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Hiya BrandonW,

    Simply change the status for this database in master..sysdatabases to 320, stop-start ASE and drop it using dbcc dbrepair.

    Cheers
    Willy

  3. #3
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353

    Re: Help removing a database that is on a device that does not exist.

    You can artificially mark your database as "suspect", and then you can use dbcc dbrepair() to drop the database:

    From a post I found on http://groups.google.net just now:



    1) The command which you should use is dbcc dbrepair() and not dbcc
    repair()

    2) You have to mark the database as 'suspect' in order to use the
    'dropdb' option. For this, start the server in single user mode.

    3) Configure the server to allow system table updations with the
    following command :

    sp_configure "allow updates", 1

    3) Next mark the database suspect as follows :

    begin tran
    update sysdatabases set status = 320 where name = "your database name"
    ---- verify that only one row has been changed, and then
    commit tran

    4) shut down the server

    5) restart the server and then the dbcc dbrepair command will work.

    Don't forget to turn the "allow updates" option back off.

    Michael

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353

    Re: Help removing a database that is on a device that does not exist.

    Originally posted by mpeppler
    You can artificially mark your database as "suspect", and then you can use dbcc dbrepair() to drop the database:

    From a post I found on http://groups.google.net just now:
    Arg!

    That's groups.google.com, of course!

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •