Remove the device from Sybase

Question

Post reply

Remove the device from Sybase

yana209

Mr or Mrs. 500

Points: 535
More actions
May 19, 2008 at 11:28 am

#99139

Hi,
I have gone through the similar post but first let me tell you the problem that I face.
I was expanding the log segment using the disk resize and alter database. There were two devices configured.
data
log_1
and I wanted to increase the log by 600Mb. Disk resize was successful and alter database I missed "log" and the command looked like this
alter database db on log_1="600M"
and it issued an error saying something like you are trying to add data into log space, use WITH OVERRIDE if it is ok. (some thing like that). I used
alter database db on log_1="600M" WITH OVERRIDE
and it created a 600MB device by the name of log_1 but of data type 🙁 now there are three devices.
data (data)
log_1 (log)
log_1 (data)
I want to delete the newly created device. And want to proceed with the expansion all over again. Please help me, I guess I messed up the live system :((
Regards,
Yasir

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 1

Does Sybase have a remove? Alter database xxx remove device yyy?

Or an emptyfile option?

In the v6.5 days, I thought you could remove a device if it didn't (and never had) data in it. However it's possible that you're just stuck with it. The device limitations were pretty severe in v6.5, which came from Sybase.

yana209 Mr or Mrs. 500 Points: 535 More actions · Answer 2

Hi Steve,

Thanks for the reply. The device that is newly created by while expanding has the same name as the log device already present. Now I want to know how can I delete the data device without affecting the log device while both are of the same name?

I was told sp_configure and then delete to work, is that so, but what about my concern of correct selection of device?

Thanks

yana209 Mr or Mrs. 500 Points: 535 More actions · Answer 3

Steve Jones - Editor (5/20/2008)
Does Sybase have a remove? Alter database xxx remove device yyy?
Or an emptyfile option?

What do they do? I'm quite new to this database thing, and I guess my start has been very bumpy :p

Vivien Xing SSChampion Points: 12297 More actions · Answer 4

I do not think you can drop a device while it is in use by a live database. log_1 device seems for both Data and Log now.

You may move the transaction log to another device (create a new one just for Log) and leave log_1 for data only. http://www.rocket99.com/techref/sybase8007.html

Test it first.

yana209 Mr or Mrs. 500 Points: 535 More actions · Answer 5

Thanks. I gather, the log device which is already present, for which I was trying to expand, I can expand that leaving this new data device as it is. Hope that doesn't affect the automatic backup scripts.

yana209 Mr or Mrs. 500 Points: 535 More actions · Answer 6

Vivien Xing (5/20/2008)
I do not think you can drop a device while it is in use by a live database. log_1 device seems for both Data and Log now.
Test it first.

Hi as you said, that we cannot drop the device once it's used, so it means if it is not, then we can. So if the available space of the device is what the total space is that means the device is not in used. Now can you tell me how do we drop the device? Dropping means removing it completely? Since there are two devices by the same name, I think there'll be some ID for each device. How can we drop/remove on the basis of ID?

Thanks...

SQLBill SSC Guru Points: 51440 More actions · Answer 7

I have never used Sybase, so I can't answer your question...but I will say this - before you do anything else, make a good backup. That way if something goes wrong and you delete the database you want to keep, you will be able to restore it.

-SQLBill

Vivien Xing SSChampion Points: 12297 More actions · Answer 8

yana209 (5/27/2008)
Vivien Xing (5/20/2008)
I do not think you can drop a device while it is in use by a live database. log_1 device seems for both Data and Log now.
Test it first.
Hi as you said, that we cannot drop the device once it's used, so it means if it is not, then we can. So if the available space of the device is what the total space is that means the device is not in used. Now can you tell me how do we drop the device? Dropping means removing it completely? Since there are two devices by the same name, I think there'll be some ID for each device. How can we drop/remove on the basis of ID?
Thanks...

--> Dropping device means you have to drop the database first, which I do not think it applies to your case.

How do you get the following output? What do you get from "sp_helpdb dbname"?

data (data)

log_1 (log)

log_1 (data)

yana209 Mr or Mrs. 500 Points: 535 More actions · Answer 9

Using sp_helpdb

I just need to remove the newly created device log_1 which is data device and after removing I'll expand the log device.

Vivien Xing SSChampion Points: 12297 More actions · Answer 10

yana209 (5/28/2008)
Using sp_helpdb
I just need to remove the newly created device log_1 which is data device and after removing I'll expand the log device.

Read through Sybase administrator guide you may have a better understanding about device.

Have you tried sybase user group? You may get some input there.

yana209 Mr or Mrs. 500 Points: 535 More actions · Answer 11

Vivien Xing (5/28/2008)
Read through Sybase administrator guide you may have a better understanding about device.
Have you tried sybase user group? You may get some input there.

user group?

No. If you can please guide me to get there, I'll be thankful.

Vivien Xing SSChampion Points: 12297 More actions · Answer 12

http://www.sybase.com/support/community-forums

http://www.teamsybase.com/usergroups.html

Hope this helps.

ziadchaker Valued Member Points: 61 More actions · Answer 13

1- move the logs to another device

2- mirror the log_1 device to another device

3- drop log_1 device