Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    35

    Angry Unanswered: What is problem with my device settings?

    Hi Expert

    My old database device shown as follows using sp_helpdb:
    old 100.0 MB data only 0
    old 100.0 MB data only 48
    old2 200.0 MB data only 3696
    old6 400.0 MB data only 236960
    oldlog 100.0 MB log only 0
    oldlog2 50.0 MB log only 0
    oldlog3 50.0 MB log only 8464
    oldlog4 50.0 MB log only 51200
    oldlog5 50.0 MB log only 51200
    oldlog6 100.0 MB log only 66064

    It is shown that datadevice contains data and log device contains log only. Then i try to migrate the database to my new machine with the Create database statement as follows(follow the config above):

    Create database new
    on new_data01=100,
    new_data01=100,
    new_data01=200,
    new_data01=400
    log on new_log01=100,
    new_log01=50,
    new_log01=50,
    new_log01=50,
    new_log01=50,
    new_log01=100
    for load
    go


    It has the log as follows:
    CREATE DATABASE: allocating 51200 pages on disk 'new_data01'
    CREATE DATABASE: allocating 51200 pages on disk 'new_data01'
    CREATE DATABASE: allocating 102400 pages on disk 'new_data01'
    CREATE DATABASE: allocating 204800 pages on disk 'new_data01'
    CREATE DATABASE: allocating 51200 pages on disk 'new_log01'
    CREATE DATABASE: allocating 25600 pages on disk 'new_log01'
    CREATE DATABASE: allocating 25600 pages on disk 'new_log01'
    CREATE DATABASE: allocating 25600 pages on disk 'new_log01'
    CREATE DATABASE: allocating 25600 pages on disk 'new_log01'
    CREATE DATABASE: allocating 51200 pages on disk 'new_log01'


    After reload from our dump device and check the sp_helpdb the device fragments are as follows:
    new_data01 100.0 MB data only 0
    new_data01 100.0 MB data only 48
    new_data01 100.0 MB data only 3200
    new_data01 200.0 MB data only 3696
    new_data01 50.0 MB log only 51200
    new_data01 100.0 MB log only 51888
    new_data01 150.0 MB log only 134528
    new_log01 300.0 MB data only 237264
    new_log01 100.0 MB log only 102400

    My new data and log device became mixed.
    Is this how sybase works? or Anything that i do not know?

    How can i make a separat data device for data and log device for log only?

    Do you have documentation how to separate them?

    I am not an experienced in Sybase, so are there anyone can help me?



    Regards,
    Mike.

  2. #2
    Join Date
    Jan 2003
    Posts
    26

    Re: What is problem with my device settings?

    I'm no expert but let me see if I can help.

    The first issue to address if you plan on loading a dump into this new db is to make sure the fragments of the new db are in the same order as the old db. sp_helpdb shows the fragments in alphabetical order so you need to run the following command as sa to get the correct order:
    select segmap, size/512 "Size in MB"
    from sysusages
    where dbid=db_id ("your database")

    segmap #'s relation to segments:
    2^0 = system
    2^1 = default
    2^2 = logsegment

    So a segment value of 7 means the fragment is being used for both data and log. With this info, you should be able to write your create database code. And I think you might need to have "log on" in each line of your code where you have a log fragment, but I could be wrong.

    After the db is created, if the new_data01 fragment still has both data and log, try running the following command as sa:
    sp_logdevice new_data01, new_log01

    This will move the log off of new_data01. Next run this:
    dbcc checktable(syslogs)

    This might give you an error because the logsegment is still on new_data01 because there haven't been enough transactions to get things onto new_log01. So do some transactions in your db (preferably not minimally logged ones like select into or bcp) until the dbcc command from above shows space used/free in the log segment. At this point you should be set to load the dump.

    Hope I got most of this right and it helps.

    Frank

Posting Permissions

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