Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    3

    Unanswered: mixed data and log on same device

    Hi,

    I've "inherited" some databases that are looking rather messy.
    They are spread out over many small segments and in a few places there is data and log on the same device.

    I've ignored this since it was not causing any real problem but after a recent upgrade from ASE 11.9.2 to 12.0.0.5 it seems it is now a real problem.

    I've experienced an 7411 error:
    #############################################
    00:00000:00001:2002/10/19 14:49:05.30 server Error: 7411, Severity: 21, State: 1
    00:00000:00001:2002/10/19 14:49:05.30 server Table 'systhresholds' in database 'cnadb' is not in its correct sort order. Either the clustered index is missing or there is data corruption in the table.
    ########################################
    And also problem with hanging databases.

    Is there a straightforward way to move the log of the datadevice or recreate the database with a simpler layout.

    Thanks in advance


  2. #2
    Join Date
    Oct 2002
    Posts
    3

    Re: mixed data and log on same device

    Sorry about the sloppy typing What I meant to type was "hanging database dumps"
    Originally posted by hepp
    Hi,

    I've "inherited" some databases that are looking rather messy.
    They are spread out over many small segments and in a few places there is data and log on the same device.

    I've ignored this since it was not causing any real problem but after a recent upgrade from ASE 11.9.2 to 12.0.0.5 it seems it is now a real problem.

    I've experienced an 7411 error:
    #############################################
    00:00000:00001:2002/10/19 14:49:05.30 server Error: 7411, Severity: 21, State: 1
    00:00000:00001:2002/10/19 14:49:05.30 server Table 'systhresholds' in database 'cnadb' is not in its correct sort order. Either the clustered index is missing or there is data corruption in the table.
    ########################################
    And also problem with hanging databases.

    Is there a straightforward way to move the log of the datadevice or recreate the database with a simpler layout.

    Thanks in advance


  3. #3
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Hiya Hepp,

    There is a techdoc available on Sybase site, that explains how to go about this one

    http://my.sybase.com/detail?id=20466

    Cheers
    Willy

  4. #4
    Join Date
    Oct 2002
    Posts
    3
    Thanks for the reply, do not wish to appear ungrateful but this is not quite he same problem that I have.
    My log is not on the same fragment but on the same device.

    Perhaps I got the terminology wrong but this is what it looks like:

    device_fragments size usage
    free kbytes
    ------------------------------ ------------- --------------------
    ----------------
    datadev1 30.0 MB data only
    26650
    datadev1 30.0 MB data only
    30024
    datadev1 60.0 MB data only
    33180
    datadev1 30.0 MB log only
    not applicable
    datadev1 50.0 MB log only
    not applicable
    logdev1 8.0 MB data only
    8160
    logdev1 50.0 MB data only
    50778
    logdev1 22.0 MB log only
    not applicable
    logdev1 25.0 MB log only
    not applicable
    logdev1 35.0 MB log only

  5. #5
    Join Date
    Nov 2002
    Posts
    1

    Re: mixed data and log on same device

    Hello heep,

    To have the log on its own device you should follow these steps:
    1. Add a new divce to that database. The size of the new device to be as much as you would like the log being.

    2.This new devices you will add will be created by default on system and default segment. You should remove this deveice from default and system segment.

    3. Add this device to logsegment.

    4. Remove from log segment the device which contains also data.

    Commands:

    1.
    disk init name="newdevice"
    .....
    go
    alter database yourdatabase on newdevice
    go
    2.
    sp_dropsegment default,yourdatabase,newdevice
    go
    sp_dropsegment system,yourdatabase,newdevice
    go
    3.
    sp_extendsegment logsegment, yourdatabase,newdevice
    go
    4.
    sp_dropsegment logsegment,yourdatabase,"the device which contains data"
    go.
    use yourdatabase
    go
    sp_helpdb yourdatabase
    go

    Bye

Posting Permissions

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