Sybase Database - sp

Author

sp_dropdevice

jeanhalle@gmail.com

2006-03-31, 8:23 pm

Hi,
I got the following error when I try to drop a device.

>sp_dropdevice mydevice

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

Nothing happen when I make a drop database.

By error, I delete the file where the devise is refering. Don't know if
it's the reason why I can't drop the database...and remove device.

Thanks everybody

Mark A. Parsons

2006-03-31, 8:23 pm

It may be that you have another database residing on this same disk.

Removing the device (out in the OS) may disable the 'drop database' ... I'm
not 100% sure.

Assuming you're running a pre-15 dataserver, try running the following
query to see what database(s) may still be sitting on your device:

-------------------
declare @mb_factor_u int

select @mb_factor_u = (1024*1024)/low
from master..spt_values
where number = 1
and type = 'E'

select db_name(u.dbid) as dbname,
d.name as device,
u.size/@mb_factor_u as 'size (MB)'
from master..sysdevices d,
master..sysusages u
where u.vstart between d.low and d.high
and d.cntrltype = 0
and d.name = '< name_of_device_in_qu
estion>'
order by 2,1
-------------------

If you're running ASE 15.0 let me know and I can post an ASE version of the
query.

jeanhalle@gmail.com wrote:
> Hi,
> I got the following error when I try to drop a device.
>
>
>
>
>
> "Device is being used by a database. You can't drop it."
>
>
> Nothing happen when I make a drop database.
>
> By error, I delete the file where the devise is refering. Don't know if
> it's the reason why I can't drop the database...and remove device.
>
> Thanks everybody
>

jeanhalle@gmail.com

2006-03-31, 8:23 pm

Thanks but, no other db seem to be on the device (according to your
query).

Mark A. Parsons

2006-03-31, 8:23 pm

OK, how about trying:

----------------------
dbcc traceon(3604)
go
-- upload current master..sysusages into
-- dataserver working memory
dbcc dbrepair('<dbname>','remap')
go
sp_dropdevice < device_name_in_quest
ion>
go
----------------------

If this still doesn't work you could try:

1 - bouncing dataserver to see if this clears up your situation

2 - if you're 100% sure the device is no longer used by any other databases
you could remove the entry from master..sysdevices (be very careful that
you only remove the one entry!)

sp_dropdevice should work without any problems ... it typically fails if
the device is, in fact, still in use by a database ... or some of the
tables in the master database have become corrupted (eg, improper manual
removal of records from master database tables).

jeanhalle@gmail.com wrote:

> Thanks but, no other db seem to be on the device (according to your
> query).
>

jeanhalle@gmail.com

2006-03-31, 8:23 pm

dbcc repair fail because the file that device is refering is missing.

I'm sure cause I create this device and the database.

May I also drop the database entry from sysdatabases?

Mark A. Parsons

2006-03-31, 8:23 pm

You *can* delete the entry from sysdatabases ... but make sure you also
remove any related entries from sysusages.

Afterwards you'll want to run the dbcc/remap command again; alternatively,
bounce your dataserver.

Obviously (?) deleting the wrong records from system tables can cause mucho
problems and headaches ... so be very careful with what you delete. (I
typically start a transaction 'begin tran', do the delete/update, run a
select to validate the changes, then issue a 'commit tran' or 'rollback
tran' depending on whether the delete/update was successful or not.)

jeanhalle@gmail.com wrote:

> dbcc repair fail because the file that device is refering is missing.
>
> I'm sure cause I create this device and the database.
>
> May I also drop the database entry from sysdatabases?
>

Sanjeev

2006-04-04, 9:23 am

You can drop a device if its not being used by a database.

Sponsored Links