Hi guys,

I inherited a server with one database that is oversize, having less than 10% of data in it. It has several devices so I would like to move data to the single device, to remove unused devices and drop them.

Question is how to remove unused device from the user database.

Thank you.

Hello,

So to drop devices, you have to first drop the DB.

So before do anything, i advise you to make a dump of the tb, and save the current devices setting present for that db - sp_helpdb "name"

So if you have to rollback your action, everything can be like before.

Now you can't use dump and load, because to load the db size has to be the same or higher.

So you options are:
1- Bcp out the tables.
You bulk copy out the tables then "drop database" , "sp_dropdevices" and drop the devices that you want to drop. And after recreate the db with the size you want, and bcp in the tables.

2- Create a new db on the server, with the size you desire, copy table data from one db to another, drop the old one, and rename (sp_renamedb) the new to the old name.



Hope it helps

That is what I was afraid of.

Bulk copy out and in is not a problem (with 200+ tables though it is not that trivial). On top of it, all indexes and keys got to be recreated after bcp in.

Thank you guys.

As dump and load is not an option at all, also I don't think there is any way to reduce the size of a database.
So the BCP seems only solution in this case.
To take care of indexes and other key constraints u need to use sybase re-engineering using power builder to genrate ddl of all the objects of the database, so the steps should be for the second option in 2nd post (which seems to be more safer)

1. Take bcp out of all the tables.
2. Genrate DDL for all the objects(table,indexes,triggers,sp etc) with the help of powerbuilder.
3. Create a new database of desired size.
4. Create the tables.
5. BCP in the data to the tables.
6 create rest of the objects using DDL that was genrated in 2nd step.
7. check if everything is fine, drop the old database and rename the new database with the name of old database.

to take the bcp of number of tables, below may help

1. execute below from sybase prompt
select 'bcp my_db'+'..',name +'out',name+'.bcp.out -UUser_name -SASE_Server_Name -Ppassword -c' from sysobjects where type='U'

2. Above will genrate the bcp command for all the user tables in the database.

3. execute them from OS prompt, to get the bcp out.


Hope this may be helpful