Results 1 to 7 of 7
  1. #1
    Join Date
    May 2013
    Posts
    5

    Unanswered: Wrong Fragment After Loading Dump

    Hi Friends,

    I decided to write my problem here because I've searched Internet, Forums and ... about 10 hours and have found many solutions that I could not use cause of having low knowledge in sybase and fear of losing data which I think will not possible or at least very harder than my current problem specially for a non expert like me.

    I try to discuss what has happened till now step by step to prevent every probable important thing for solving my problem. I even attached all the info that seems important for solving my problem based on what I've found till now on different threads in different forums.

    And at last, please give as much details as possible about your solution (including steps, commands and ...) because as I told before I'm not a sybase expert.

    OK, Here is my problem:

    -We had 2 Sybase ASE server (1 operational and 1 offline-backup)
    -Our operational server has a backup mechanism (using windows batches) to take a full database dump and also 6 transaction every 4 hours.
    -Using our NMS installation document for installing DB Server, I reinstalled our offline back-up from the scratch to change RAID structure and also configuring and optimizing Sybase parameters based on what was recommended in this document. This recommendation is include increasing in data and log devices size and some other for optimizing memory.
    -Everything goes well and we switched into this server till we could do this changes on main one too.
    -after 4 days when I goes to do our weekly maintenance based on another document from our NMS company I found that output of sp_helpdb has been changed and after searching on different sites I found it is related to sybase behavior during moving dump to new device.
    -As I told before we have switched into new server and our DB and also our backup size and structure has been changed so I can not switch back to previous server.
    -At below you can find previous server settings:
    Code:
    1> sp_helpdb nmsdb
    2> go
     name   db_size       owner dbid created      durability lobcomplvl inrowlen status
     ------ ------------- ----- ---- ------------ ---------- ---------- -------- ----------------------
     nmsdb     6000.0 MB sa       4 May 10, 2012 full                0     NULL abort tran on log full
    
    (1 row affected)
     name   attribute_class attribute     int_value char_value   comments
     ------ --------------- ------------- --------- ------------ --------
     nmsdb buffer manager  cache binding         1 nmsdb_cache NULL
     device_fragments               size          usage                created                   free kbytes
     ------------------------------ ------------- -------------------- ------------------------- ----------------
     nms_db1                            2000.0 MB data only            May 10 2012  8:08PM                 771162
     nms_log1                           4000.0 MB log only             May 10 2012  8:08PM       not applicable
    
     ----------------------------------------------------------------------
     log only free kbytes = 4075896
    (return status = 0)
    1> sp_helpdevice
    2> go
     device_name physical_name                description                                                                                                status cntrltype vdevno vpn_low vpn_high
     ----------- ---------------------------- ---------------------------------------------------------------------------------------------------------- ------ --------- ------ ------- --------
     master      D:\SYBASE\DATA\master.dat    file system device, special, dsync on, directio off, default disk, physical disk, 511.00 MB, Free: 1.00 MB      3         0      0       0   261631
     nms_db1     e:\sybase\data\nmsdb1.dat    file system device, special, dsync off, directio on, physical disk, 10240.00 MB, Free: 8240.00 MB               2         0      4       0  5242879
     nms_log1    f:\sybase\data\nmsdb1.log    file system device, special, dsync off, directio on, physical disk, 20480.00 MB, Free: 16480.00 MB              2         0      5       0 10485759
     sysprocsdev D:\SYBASE\DATA\sybprocs.dat  file system device, special, dsync off, directio on, physical disk, 300.00 MB, Free: 0.00 MB                    2         0      1       0   153599
     systemdbdev D:\SYBASE\DATA\sybsysdb.dat  file system device, special, dsync off, directio on, physical disk, 6.00 MB, Free: 0.00 MB                      2         0      3       0     3071
     tapedump1   \\.\TAPE0                    unknown device type, disk, dump device                                                                         16         2      0       0    20000
     tapedump2   \\.\TAPE1                    unknown device type, tape,        625 MB, dump device                                                          16         3      0       0    20000
     temp_db1    d:\sybase\data\tempdb1.dat   file system device, special, dsync off, directio on, physical disk, 2000.00 MB, Free: 0.00 MB                   2         0      6       0  1023999
     temp_log1   d:\sybase\data\tempdb1.log   file system device, special, dsync off, directio on, physical disk, 1000.00 MB, Free: 0.00 MB                   2         0      7       0   511999
     tempdbdev   D:\SYBASE\DATA\tempdbdev.dat file system device, special, dsync off, directio on, physical disk, 100.00 MB, Free: 0.00 MB                    2         0      2       0    51199
    
    (10 rows affected)
    (return status = 0)

  2. #2
    Join Date
    May 2013
    Posts
    5

    wrong fragment after loading dump

    +++continue cause of text size limitation+++
    -And current server information (more complete):
    Code:
    1> sp_helpdevice
    2> go
     device_name physical_name                description                                                                                                 status cntrltype vdevno vpn_low vpn_high
     ----------- ---------------------------- ----------------------------------------------------------------------------------------------------------- ------ --------- ------ ------- --------
     master      D:\SYBASE\DATA\MASTER.DAT    file system device, special, dsync on, directio off, default disk, physical disk, 2059.00 MB, Free: 1.00 MB      3         0      0       0  1054207
     nms_db1     E:\SYBASE\DATA\NMSDB1.DAT    file system device, special, dsync off, directio on, physical disk, 20480.00 MB, Free: 480.00 MB                 2         0      4       0 10485759
     nms_log1    F:\SYBASE\DATA\NMSDB1.LOG    file system device, special, dsync off, directio on, physical disk, 40960.00 MB, Free: 960.00 MB                 2         0      5       0 20971519
     sysprocsdev D:\SYBASE\DATA\SYBPROCS.DAT  file system device, special, dsync off, directio on, physical disk, 300.00 MB, Free: 0.00 MB                     2         0      1       0   153599
     systemdbdev D:\SYBASE\DATA\SYBSYSDB.DAT  file system device, special, dsync off, directio on, physical disk, 6.00 MB, Free: 0.00 MB                       2         0      3       0     3071
     tapedump1   \\.\TAPE0                    unknown device type, disk, dump device                                                                          16         2      0       0    20000
     tapedump2   \\.\TAPE1                    unknown device type, tape,        625 MB, dump device                                                           16         3      0       0    20000
     temp_db1    D:\SYBASE\DATA\TEMPDB1.DAT   file system device, special, dsync off, directio on, physical disk, 5000.00 MB, Free: 0.00 MB                    2         0      6       0  2559999
     temp_log1   D:\SYBASE\DATA\TEMPDB1.LOG   file system device, special, dsync off, directio on, physical disk, 2000.00 MB, Free: 0.00 MB                    2         0      7       0  1023999
     tempdbdev   D:\SYBASE\DATA\TEMPDBDEV.DAT file system device, special, dsync off, directio on, physical disk, 100.00 MB, Free: 0.00 MB                     2         0      2       0    51199
    
    (10 rows affected)
    (return status = 0)
    1> sp_helpdb
    2> go
     name           db_size       owner dbid  created      durability  lobcomplvl inrowlen status
     -------------- ------------- ----- ----- ------------ ----------- ---------- -------- ---------------------------------------------------------------------
     nmsdb            60000.0 MB sa        4 Apr 16, 2013 full                 0     NULL abort tran on log full
     master             2048.0 MB sa        1 Apr 16, 2013 full                 0     NULL mixed log and data
     model                 3.0 MB sa        3 Apr 16, 2013 full                 0     NULL mixed log and data
     sybsystemdb           9.0 MB sa    31513 Apr 16, 2013 full                 0     NULL trunc log on chkpt, mixed log and data
     sybsystemprocs      300.0 MB sa    31514 Apr 16, 2013 full                 0     NULL trunc log on chkpt, mixed log and data
     tempdb             7104.0 MB sa        2 May 05, 2013 no_recovery          0     NULL select into/bulkcopy/pllsort, trunc log on chkpt, allow wide dol rows
    
    (1 row affected)
     name   attribute_class attribute     int_value char_value   comments
     ------ --------------- ------------- --------- ------------ --------
     tempdb buffer manager  cache binding         1 tempdb_cache NULL
     nmsdb buffer manager  cache binding         1 nmsdb_cache NULL
    (return status = 0)
    1> sp_helpdevice nms_db1
    2> go
     device_name physical_name             description                                                                                      status cntrltype vdevno vpn_low vpn_high
     ----------- ------------------------- ------------------------------------------------------------------------------------------------ ------ --------- ------ ------- --------
     nms_db1     E:\SYBASE\DATA\NMSDB1.DAT file system device, special, dsync off, directio on, physical disk, 20480.00 MB, Free: 480.00 MB      2         0      4       0 10485759
    
    (1 row affected)
     dbname size          allocated           vstart  lstart
     ------ ------------- ------------------- ------- -------
     nmsdb    2000.00 MB May  5 2013  4:04PM       0       0
     nmsdb    4000.00 MB May  5 2013  4:04PM 1024000 1024000
     nmsdb   14000.00 MB May  5 2013  4:04PM 3072000 3072000
    
    (1 row affected)
    (return status = 0)
    1> sp_helpdevice nms_log1
    2> go
     device_name physical_name             description                                                                                      status cntrltype vdevno vpn_low vpn_high
     ----------- ------------------------- ------------------------------------------------------------------------------------------------ ------ --------- ------ ------- --------
     nms_log1    F:\SYBASE\DATA\NMSDB1.LOG file system device, special, dsync off, directio on, physical disk, 40960.00 MB, Free: 960.00 MB      2         0      5       0 20971519
    
    (1 row affected)
     dbname size          allocated           vstart lstart
     ------ ------------- ------------------- ------ --------
     nmsdb   40000.00 MB May  5 2013  4:04PM      0 10240000
    
    (1 row affected)
    (return status = 0)
    1> use nmsdb
    2> go
    1> sp_helpsegment logsegment
    2> go
     segment name       status
     ------- ---------- ------
           2 logsegment      0
     device   size
     -------- ---------
     nms_db1  4000.0MB
     nms_log1 40000.0MB
     free_pages
     -----------
        22439117
    
    Objects on segment 'logsegment':
    
     table_name index_name indid partition_name
     ---------- ---------- ----- --------------
     syslogs    syslogs        0 syslogs_8
    
    Objects currently bound to segment 'logsegment':
    
     table_name index_name indid
     ---------- ---------- -----
     syslogs    syslogs        0
    
     total_size        total_pages     free_pages      used_pages      reserved_pages
    
     ----------------- --------------- --------------- --------------- ---------------
    
     44000.0MB         22528000        22439117        88883           0
    
    (return status = 0)
    1> select lct_admin('logsegment_freepages',db_id()) as 'free_pages',
    2> lct_admin('reserved_for_rollbacks',db_id()) as 'reserved_pages',
    3> lct_admin('num_logpages',db_id()) as 'num_used_pages'
    4> go
     free_pages  reserved_pages num_used_pages
     ----------- -------------- --------------
        22438925              0           1069
    
    (1 row affected)
    1> select * from sysusages where dbid=4
    2> go
     dbid   segmap      lstart      size        vstart      location unreservedpgs crdate                          vdevno
     ------ ----------- ----------- ----------- ----------- -------- ------------- ------------------------------- -----------
          4           3           0     1024000           0        0        385581             May  5 2013  4:04PM           4
          4           4     1024000     2048000     1024000        0       2040000             May  5 2013  4:04PM           4
          4           3     3072000     7168000     3072000        0       7140000             May  5 2013  4:04PM           4
          4           4    10240000    20480000           0        0      20400000             May  5 2013  4:04PM           5
    
    (4 rows affected)

  3. #3
    Join Date
    May 2013
    Posts
    5

    wrong fragment after loading dump

    +++continue cause of text size limitation+++
    -and what that should be based on installation document:
    Code:
    1> sp_helpdb nmsdb
    2> go
     name   db_size       owner dbid created      durability lobcomplvl inrowlen status
     ------ ------------- ----- ---- ------------ ---------- ---------- -------- ----------------------
     nmsdb     60000.0 MB sa       4 May 10, 2012 full                0     NULL abort tran on log full
    
    (1 row affected)
     name   attribute_class attribute     int_value char_value   comments
     ------ --------------- ------------- --------- ------------ --------
     nmsdb buffer manager  cache binding         1 nmsdb_cache NULL
     device_fragments               size          usage                created                   free kbytes
     ------------------------------ ------------- -------------------- ------------------------- ----------------
     nms_db1                            20000.0 MB data only            May 10 2012  8:08PM                 771162
     nms_log1                           40000.0 MB log only             May 10 2012  8:08PM       not applicable
    
     ----------------------------------------------------------------------
     log only free kbytes = 4075896
    (return status = 0)
    1> sp_helpdevice
    2> go
     device_name physical_name                description                                                                                                status cntrltype vdevno vpn_low vpn_high
     ----------- ---------------------------- ---------------------------------------------------------------------------------------------------------- ------ --------- ------ ------- --------
     master      D:\SYBASE\DATA\master.dat    file system device, special, dsync on, directio off, default disk, physical disk, 511.00 MB, Free: 1.00 MB      3         0      0       0   261631
     nms_db1     e:\sybase\data\nmsdb1.dat    file system device, special, dsync off, directio on, physical disk, 20480.00 MB, Free: xxxx.00 MB               2         0      4       0  5242879
     nms_log1    f:\sybase\data\nmsdb1.log    file system device, special, dsync off, directio on, physical disk, 40960.00 MB, Free: xxxx.00 MB              2         0      5       0 10485759
     sysprocsdev D:\SYBASE\DATA\sybprocs.dat  file system device, special, dsync off, directio on, physical disk, 300.00 MB, Free: 0.00 MB                    2         0      1       0   153599
     systemdbdev D:\SYBASE\DATA\sybsysdb.dat  file system device, special, dsync off, directio on, physical disk, 6.00 MB, Free: 0.00 MB                      2         0      3       0     3071
     tapedump1   \\.\TAPE0                    unknown device type, disk, dump device                                                                         16         2      0       0    20000
     tapedump2   \\.\TAPE1                    unknown device type, tape,        625 MB, dump device                                                          16         3      0       0    20000
     temp_db1    d:\sybase\data\tempdb1.dat   file system device, special, dsync off, directio on, physical disk, 2000.00 MB, Free: 0.00 MB                   2         0      6       0  1023999
     temp_log1   d:\sybase\data\tempdb1.log   file system device, special, dsync off, directio on, physical disk, 1000.00 MB, Free: 0.00 MB                   2         0      7       0   511999
     tempdbdev   D:\SYBASE\DATA\tempdbdev.dat file system device, special, dsync off, directio on, physical disk, 100.00 MB, Free: 0.00 MB                    2         0      2       0    51199
    
    (10 rows affected)
    (return status = 0)
    -I want nms_db1 (data only fragment) resides on nms_db1.dat and nms_log1 (log only fragment) resides on nms_db1.log and the only different was bigger fragments and devices size of them.
    -any help would be really appreciated
    Last edited by Gondelat; 05-09-13 at 09:33.

  4. #4
    Join Date
    May 2013
    Posts
    5
    no response?

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Your target database for a load must have the same data and log segment sizes else you end up with the situation you have
    e.g. source db is
    10M data1
    1M log1
    2M log1
    2M data2
    5M data3
    4M log2

    To properly create target DB with larger devices ensure the data and log sizes are the same (note data2 and data3 can be combined as it is for the same segment i.e. data. Same for the 1M and 2M log)
    create targetdb on data1=10, log1=3, data1=7, log1=4 for load
    Then load the db and if needed alter the db to enlarge

    You are fortunate that no data landed on a log device else the fix is not easy (data is not moved when you drop a segment).
    To fix your problem just drop the log segment from the data device and extend the default and system segments e.g.
    sp_dropsegment 'logsegment', 'nmsdb', 'nms_db1'
    sp_extendsegment default, 'nmsdb', 'nms_db1'
    sp_extendsegment 'system', 'nmsdb', 'nms_db1'

  6. #6
    Join Date
    May 2013
    Posts
    5
    First: thanks a lot for your response.

    Second I searched as much as about this thing during these days that I think I can beat Google for finding related forum about this problem. I can not stay quiet about this manner that sybase default way in loading dump and also having no feature (like a switch) for load command that allow selecting destination devices for data and log part in separately is how much strange IMHO. Now I have many other questions:

    At below is what I did exactly to make your important command lines works. I have learned many of them and what they do during these damn days:

    use master
    sp_dboption nmsdb,'single user',true
    go
    use nmsdb
    go
    checkpoint
    go
    sp_dropsegment 'logsegment', 'nmsdb', 'nms_db1'
    go
    sp_extendsegment 'default', 'nmsdb', 'nms_db1' --> default needs
    go
    sp_extendsegment 'system', 'nms', 'nms_db1'
    go

    now my sp_helpdb nmsdb output is like this:

    Code:
     name   db_size       owner dbid created      durability lobcomplvl inrowlen status
     ------ ------------- ----- ---- ------------ ---------- ---------- -------- -------------------------------
     nmsdb    15625.0 MB sa       4 May 10, 2013 full                0     NULL single user, mixed log and data
    
    (1 row affected)
     device_fragments               size          usage                created                   free kbytes
     ------------------------------ ------------- -------------------- ------------------------- ----------------
     nms_db1                            2000.0 MB data only            May 10 2013  4:04PM                861520
     nms_db1                            4000.0 MB data only            May 10 2013  4:04PM                4079984
     nms_db1                            14000.0 MB data only            May 10 2013  4:04PM                14280000
     nms_log1                           40000.0 MB log only             May 10 2013  4:04PM       not applicable
    
     ----------------------------------------------------------------------
     log only free kbytes = 44859052
     device   segment
     -------- ----------
     nms_db1  default
     nms_db1  system
     nms_log1 logsegment
    (return status = 0)
    Does it mean I have to dump,create another db,and load dump if I want to have them all data parts merged (having 1 line for data only in sp_helpdb nmsdb output like before?)

    Will this change have any pros in comparison with its current state?

    Do I lose any information by droping log segment at first step?

    And at last, I didn't see any command that tell reside nmsdb log on new device (nms_log1) after droping previous segment . how sybase know that it should switch to this? I mean if it is default behavior or I should do it myself?

    Yours Faithfully
    Gondelat

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by Gondelat View Post
    Does it mean I have to dump,create another db,and load dump if I want to have them all data parts merged (having 1 line for data only in sp_helpdb nmsdb output like before?)
    Yes, dump db, drop & recreate db, load db.

    Quote Originally Posted by Gondelat View Post
    Will this change have any pros in comparison with its current state?
    No

    Quote Originally Posted by Gondelat View Post
    Do I lose any information by droping log segment at first step?

    And at last, I didn't see any command that tell reside nmsdb log on new device (nms_log1) after droping previous segment . how sybase know that it should switch to this? I mean if it is default behavior or I should do it myself?
    No, Sybase will automatically swap to 2nd device as 1st device become full.
    If the log was busy on 1st device when you dropped the segment (see logptr), you can force it to the 2nd device by doing a dump tran.

    You can check the logptr on sysdatabases to see which device fragment is currently in use by the log

    Code:
    select dbname=db.name, db.logptr, u.lstart, lend=u.lstart+u.size-1
    , u.segmap, dev.name
    from sysusages u
    join sysdevices dev
      on u.vdevno=dev.vdevno
     and dev.status&2=2
    join sysdatabases db
      on u.dbid=db.dbid
     and db.logptr between u.lstart and u.lstart+u.size-1
    Last edited by pdreyer; 05-13-13 at 07:23.

Posting Permissions

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