Hello All;

I have a problem that I hope someone has a solution to.

I have sybase running on RHEL5 and a database was created thats 10gigabytes in size on four devices.

This was the script used:
=======================================
use master
go
disk init
name='conpro01_dat',
physname='/mnt/sda1/databasedevices/conpro01_dat',
size='3500M'
go

disk init
name='conpro02_dat',
physname='/mnt/sda1/databasedevices/conpro02_dat',
size='3500M'
go

disk init
name='conpro01_ndx',
physname='/mnt/sda1/databasedevices/conpro01_ndx',
size='1500M'
go

disk init
name='conpro01_log',
physname='/mnt/sda1/logdevices/conpro01_log',
size='1500M'
go

create database conpro
on
conpro01_dat=3500,
conpro02_dat=3500,
conpro01_ndx=1500
log on
conpro01_log=1500

use conpro
go
sp_addsegment seg_index,conpro,conpro01_ndx
go
sp_dropsegment 'default',conpro,conpro01_ndx
go

use master
go
sp_dboption 'conpro','select into/bulkcopy/pllsort',true
go
sp_dboption 'conpro','trunc log on chkpt',true
go

===============================
On running the script, the result was far from what was expected.
Dbartisan was used to run the script from a windows client.

View Below:
================================================== ======

device_fragments size usage created free kbytes
conpro01_dat 600.0 MB data only Aug 16 2010 186622
conpro01_dat 200.0 MB data only Aug 16 2010 203874
conpro01_dat 200.0 MB log only Aug 16 2010 not applicable
conpro01_dat 2500.0 MB data only Aug 16 2010 2547498
conpro02_dat 3500.0 MB data only Aug 16 2010 3570000
conpro01_ndx 1500.0 MB data only Aug 16 2010 1530000
conpro01_log 1500.0 MB data only Aug 16 2010 1530000


log only free kbytes = 203998
==================================

I then used dbartisan to pull up a ddl to see what may have happened and this is the result:
=============================


USE master
go
CREATE DATABASE conpro
ON conpro01_dat=600,
conpro01_dat=200
LOG ON conpro01_dat=200
go
ALTER DATABASE conpro
ON conpro01_dat=2500
go
ALTER DATABASE conpro
ON conpro02_dat=3500
go
ALTER DATABASE conpro
ON conpro01_ndx=1500
go
ALTER DATABASE conpro
ON conpro01_log=1500
go
USE master
go
EXEC sp_dboption 'conpro','select into/bulkcopy/pllsort',true
go
EXEC sp_dboption 'conpro','trunc log on chkpt',true
go
USE conpro
go
CHECKPOINT
go
USE conpro
go
EXEC sp_changedbowner 'sa'
go
EXEC sp_addsegment 'default','conpro','conpro01_dat'
go
EXEC sp_extendsegment 'default','conpro','conpro01_log'
go
EXEC sp_extendsegment 'default','conpro','conpro01_ndx'
go
EXEC sp_extendsegment 'default','conpro','conpro02_dat'
go
EXEC sp_addsegment 'logsegment','conpro','conpro01_dat'
go
EXEC sp_addthreshold 'conpro','logsegment',6016,'sp_thresholdaction'
go
EXEC sp_addsegment 'seg_index','conpro','conpro01_dat'
go
EXEC sp_addsegment 'system','conpro','conpro01_dat'
go
EXEC sp_extendsegment 'system','conpro','conpro01_log'
go
EXEC sp_extendsegment 'system','conpro','conpro01_ndx'
go
EXEC sp_extendsegment 'system','conpro','conpro02_dat'
go

=================================

From this, what the server created was quite off from what was intended.

could someone give a solution on how this could have happened and how to avoid it next time but most importantly how this can be reversed to what was originally intended.

It would be highly appreciated.

A database load was done from a database that did not match the device fragments from your script.

Thanks so much for your response. I did confirm that a load was done on it.

Is there any way this can be reversed without disrupting usage?

Thanks again...

When you create a database for load you must create the different segments in the same sequence as the source database
i.e. If the source db has
500 data
100 log
500 data
300 data
200 data
200 log

you can
create database newdb
on datadev=500
,logdev=100
,datadev=1000
,logdev=200
for load

This will ensure that data segments land on data devices an log segments land on log devices.

Thanks Pdreyer

I was actually reading through one of your earlier posts. I just needed a quick way to reverse this or bring it to a point where it wont be a problem down the line as this is already in production. Your guidance would be a great relief.

The safest solution is to take a backup of the db as it stands
Drop and recreate the database in the same sequence as originally created (the one from the previous load)
Load the database using the backup taken in first step.

To find the correct sequence to use you can