Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    72

    Unanswered: mixed data & log segment

    Hi,
    How do I identify if a database has mixed data and log segments.

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    sp_helpdb <dbname>

    it will show data and log if its mixed

  3. #3
    Join Date
    Nov 2004
    Posts
    2
    HI there

    i'm running Sybase 11.9.2 and another systems administrator had installed my database with mixed log and data...so datadevice is data and log and so is log device.

    I'm a bit new to DBA stuff, but what would be the easiest to do to sort the problem out? i don't want to crash anything because it is our live server for music.

    THanks alot for any info given,

    NIRI

  4. #4
    Join Date
    Nov 2007
    Posts
    3
    hi

    to seperate the data and log

    1) create a new device with
    disk init name ="diskname" physname="path to device" , size="no"

    2) Moving the log to newly created device

    alter database <database name> log on "newly created device"

    sp_logdevice databasename, devicename

    this two steps will help you in moving your log to different device.

    3) to confirm that run sp_helplog

  5. #5
    Join Date
    Feb 2004
    Posts
    9
    Just a quick addition,

    create a dummy table to move the log pointer to the new device:
    create table dummy (col1 char(1))
    go

    insert into dummy values("a")

    go 1000

    dump tran <databasename> with truncate_only

    To make sure we clear any log that might remain on the data device

    use sp_helplog to make sure that the log starts on the new log device

  6. #6
    Join Date
    Jul 2008
    Posts
    1

    Need a Clean Seperation of Data and Log

    Hi There,

    I am pretty new to sybase (note the user name) lol, and i have a database that has log showing up on a data device and vice versa, I would like to recreate the database and load back the data from the previous database with new devices but i need to have a clean separation of the log and data. Can anyone help me, thanks so much in advance.

    shared_data 20.0 MB data only
    shared_data 10.0 MB log only
    shared_log 5.0 MB log only
    shared_log 10.0 MB data only

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You should have started a new thread for your problem.
    Create the segments in the same sequence using the output from
    select * from sysusages where dbid=db_id('dbname') order by lstart

    Asuming it is the same order as what you listed, the below create db will fix it
    create database dbname on
    shared_data=20
    ,shared_log=15 -- note you can sum if they follow each outher
    ,shared_data=10
    for load

Posting Permissions

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