Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2010
    Location
    Nigeria
    Posts
    5

    Unanswered: Erroneous Database creation

    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.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by regen
    could someone give a solution on how this could have happened
    A database load was done from a database that did not match the device fragments from your script.

  3. #3
    Join Date
    Aug 2010
    Location
    Nigeria
    Posts
    5
    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...

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Aug 2010
    Location
    Nigeria
    Posts
    5
    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.

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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
    Code:
    select dbid, segmap, lstart, sizeMB=size/(1024*1024/@@maxpagesize) 
    from sysusages 
    where dbid=db_id('sourcedb') 
    order by lstart

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •