Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Posts
    3

    Question Unanswered: Mixed data and log usage - how to separate cleanly?

    Hi, been googling but not much success. Hope a Sybase expert here might be able to help.

    I have a database which has 2 devices, Data1 (intended for data) and Log1 (intended for log). However, over time, it seems they have become mixed up and recently it required extending due to running low of space.

    This is the sp_helpdb output for my database
    Code:
    1> sp_helpdb DBNAME
    2> go
     name                     db_size       owner                    dbid
             created
             status
    
     ------------------------ ------------- ------------------------ ------
             --------------
             -----------------------------------------------------------------------
    -------------------------------
     DBNAME                      700.0 MB fxnyuser                     30
             Oct 29, 2007
             select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log ful
    
             l
    
    (1 row affected)
     device_fragments               size          usage
             created             free kbytes
     ------------------------------ ------------- --------------------
             ------------------- ----------------
     Data1                               200.0 MB data only
             Jan 21 2005  16:28PM            23374
     Data1                                50.0 MB log only
             Jan 21 2005  16:28PM not applicable
     Data1                                50.0 MB data only
             Jan 21 2005  16:28PM            21724
     Data1                                 5.0 MB log only
             Jan 21 2005  16:28PM not applicable
     Data1                                50.0 MB data only
             Jan 21 2005  16:28PM             6222
     Data1                                10.0 MB log only
             Jan 21 2005  16:28PM not applicable
     Data1                                35.0 MB data only
             Jan 21 2005  16:28PM             5074
     Log1                                 65.0 MB data only
             Jan 21 2005  16:28PM            41966
     Log1                                 35.0 MB log only
             Jan 21 2005  16:28PM not applicable
     Data1                               100.0 MB data and log
             Oct 23 2007  3:34PM           101396
     Log1                                100.0 MB log only
             Oct 23 2007  3:34PM not applicable
             
     --------------------------------------------------------------
     log only free kbytes = 197078
    (return status = 0)
    Why did it generate a Data1 device for usage by BOTH "data and log" (highlighted in bold) when I extended the database?
    I used:
    Code:
    alter database DBNAME on Data1=100 log on Log1=100
    Is it possible to force this new segment to be data only? Since the extra 100MB of log on Log1 is more than enough.

    As you can see, data usage has become mixed onto both Data1 and Log1 devices, and similarly for log. The ideal scenario would be to have data usage only on Data1 and log usage only on Log1. How can I achieve this from its current state?

    For Sybase experts that see this, is there anything to worry about? I'm new to the team so I'm taking over what someone has left behind, but Sybase isn't my expertise...many thanks in advance for your help!

    R

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by choc_mouse
    Why did it generate a Data1 device for usage by BOTH "data and log"
    The Data1 device already have data an log segments
    Quote Originally Posted by choc_mouse
    Is it possible to force this new segment to be data only?
    Yes, use sp_dropsegment to drop the log segment
    Quote Originally Posted by choc_mouse
    The ideal scenario would be to have data usage only on Data1 and log usage only on Log1. How can I achieve this from its current state?
    You need to drop and recreate the database in the same sequance it was originally created

    select * from sysusages where dbid=db_id('DBNAME') order by lstart
    a segmap of 4 indicate log only
    if you have e.g.
    segmap size
    3 10
    4 5
    3 20
    3 15
    4 5

    you can sum sizes where the segmap doesn't change then
    create database DBNAME
    on Data1=10
    ,Log1=5
    ,Data1=35 -- 20+15
    ,log1=5
    for load

    After you load the database all should be OK
    Quote Originally Posted by choc_mouse
    is there anything to worry about?
    Recovery might be an issue but since you run with 'trunc log on chkpt' you don't have a usable log in any case.
    For performance you should split the data and log device onto different disks

Posting Permissions

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