Hai all,

I'm very new to the Sybase database.Kindly consider and give me a solution for this.

I have got a database of huge size.but its showing a lot of free space. I give the database statistics below.


name db_size owner dbid created status
---- ------- ----- ----------- ------- ------
Tidco_db 9010.0 MB sa 5 Apr 15, 2003 no options set

device_fragments size usage free kbytes
---------------- ---- ----- -----------
master 10.0 MB data only 16
tidco_dev 4490.0 MB data only 4111808
tidco_dev 510.0 MB log only 257200
tidco_dev1 8.0 MB data and log 8192
tidco_dev1 992.0 MB log only 677408
tidco_dev2 8.0 MB data and log 8192
tidco_dev2 1492.0 MB data and log 1527808
tidco_dev2 1000.0 MB data only 1024000
tidco_dev2 500.0 MB log only 512000

My questions are,

* I want to reuse that free space available in the database devices.How is it possible? Can I resize the database device?

*my log and data are mixed up in some devices.can I separate them now.

need help immedietly.Eagerly waiting for a quick reply.

thanx in advance,
Niranjan.

Hi,

Sorry to hear about your dilemma. The supported method of reducing the size of the database is to script the objects & permissions, bcp the data out of the tables. Then drop and recreate the database, recreate the tables, stored procs and views, bcp the data in, create the index and then the triggers.

By correctly allocating space on the devices you will sort out your mixed up segments as well.

Potentially there are methods of doing this without dropping the database but they are not recommended for the faint hearted because it involves hacking the system tables after performing what amounts to a large amount of system administration. Considering this it is probably quicker and easier to perform the drop and recreate above.

Sorry to be the bringer of bad news.

Richard