Dropping device without prior dropping database

[Former Member]

Hi there,

Is there anyway to remove a device without removing database as the database still in use. thanks in advance.

1> sp_dropdevice QA1_data05

2> go

Msg 17482, Level 16, State 1:

Server 'QA1, Procedure 'sp_dropdevice', Line 110:

Device is being used by a database. You can't drop it.

database_name           device                size

----------------------- --------------------- ----------------------------

QA1                     QA1_data05          10240 MB

Regards,

AM

[Former Member · Accepted solution]

Prior to ASE 16.0, the only safe method is to dump the database, drop the database, recreate the database as the same original size (while not using the device that you want to drop), then reloading the database.

If you have ASE 16, you can shrink the database to not use that device expecting that other devices can hold the data that was on the device that you want to shrink.

[former_member182259 · Accepted solution]

....or 15.7 sp100 (when shrink database was added).    See ASE 15.7 New Features Guide sp100.