Results 1 to 9 of 9
  1. #1
    Join Date
    May 2009
    Posts
    43

    Unanswered: mixed log and data

    Hi,

    Few devices in my server, though they are used only for data it's showing like "mixed log and data". (The output of sp_helpdb, is also showing as mixed log and data).

    But the devices are either used for data or log but not for both.
    can any of you let me know the reasons for it and also how do I change this?

    Please revert me as soon as possible.

    Thanks,
    Vineela

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Did you restore the database from a dump of another database?
    What is the output from
    select * from sysusages
    where dbid=<your_database_id>
    order by lstart

  3. #3
    Join Date
    May 2009
    Posts
    43
    No. I didn't restore the database from a dump of another database. Here is the output of the query you asked

    dbid segmap lstart size vstart pad unreservedpgs crdate vdevno
    ------ ----------- ----------- ----------- ----------- ------ ------------- -------------------------- -----------
    9 3 0 4352000 0 NULL 95316 Jun 7 2008 5:56PM 28
    9 4 4352000 768000 0 NULL 364799 Jun 7 2008 5:56PM 30
    9 3 5120000 3584000 1638400 NULL 79793 Jun 7 2008 5:56PM 31
    9 4 8704000 256000 768000 NULL 254992 Jun 7 2008 5:56PM 30
    9 3 8960000 768000 4352000 NULL 15092 Jun 7 2008 5:57PM 28
    9 3 9728000 512000 5222400 NULL 9401 Jun 7 2008 5:57PM 31
    9 3 10240000 3072000 2048000 NULL 1942843 Jun 7 2008 5:57PM 46

    Please revert me as soon as possible.

    Thanks in advance.

    Regards,
    Vineela

  4. #4
    Join Date
    May 2009
    Posts
    43
    dbid segmap lstart size vstart pad unreservedpgs crdate vdevno
    ------ ----------- ----------- ----------- ----------- ------ ------------- -------------------------- -----------
    9 3 0 4352000 0 NULL 95316 Jun 7 2008 5:56PM 28
    9 4 4352000 768000 0 NULL 364799 Jun 7 2008 5:56PM 30
    9 3 5120000 3584000 1638400 NULL 79793 Jun 7 2008 5:56PM 31
    9 4 8704000 256000 768000 NULL 254992 Jun 7 2008 5:56PM 30
    9 3 8960000 768000 4352000 NULL 15092 Jun 7 2008 5:57PM 28
    9 3 9728000 512000 5222400 NULL 9401 Jun 7 2008 5:57PM 31
    9 3 10240000 3072000 2048000 NULL 1942843 Jun 7 2008 5:57PM 46

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    It will be useful to put code tags around your data to keep alignment i.e.
    [ code]
    data...
    data...
    [ /code]
    without the space after the '['

    Your vstart of 0 for the first 2 seem wrong - both data segment and log segment starting on the same device at the same offset. Did you edit your system tables?
    Code:
    dbid    segmap       lstart       size          vstart
    ------  -----------  -----------  -----------   -----------
          9           3            0      4352000             0
          9           4      4352000       768000             0
          9           3      5120000      3584000       1638400
          9           4      8704000       256000        768000
          9           3      8960000       768000       4352000
          9           3      9728000       512000       5222400
          9           3     10240000      3072000       2048000

  6. #6
    Join Date
    May 2009
    Posts
    43
    So how does it works now?
    Can I update the sysusages table and set vstart= 4352000 where vstart=0 and segmap=4. I think after doing this, we need to update for the remaining also (as all they are interlinked like one segmap size will become another segmap vstart.

    Let me know whether I can do like this or any other solution. But right now log and data are on the separate devices and still showing like mixed log and data.

    Please revert me as soon as possible.

    Thank you,
    Vineela

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You should not update system tables.
    Recreate the database and specify data and log in the same size & sequence as from sysusages
    Then reload the database dump

  8. #8
    Join Date
    May 2009
    Posts
    43
    dbid segmap lstart size vstart pad unreservedpgs crdate vdevno
    ------ ----------- ----------- ----------- ----------- ------ ------------- -------------------------- -----------
    9 3 0 4352000 0 NULL 95316 Jun 7 2008 5:56PM 28
    9 4 4352000 768000 0 NULL 364799 Jun 7 2008 5:56PM 30
    9 3 5120000 3584000 1638400 NULL 79793 Jun 7 2008 5:56PM 31
    9 4 8704000 256000 768000 NULL 254992 Jun 7 2008 5:56PM 30
    9 3 8960000 768000 4352000 NULL 15092 Jun 7 2008 5:57PM 28
    9 3 9728000 512000 5222400 NULL 9401 Jun 7 2008 5:57PM 31
    9 3 10240000 3072000 2048000 NULL 1942843 Jun 7 2008 5:57PM 46


    From the above output, here data and log are on separate devices.

    Log is on the vdev #30. and data on vdev #28. Though the vstart is zero for two devices (one data dev and on log dev), both are different devices.
    Data and log are on separate devices but still showing mixed log and data

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Can you show the output from sysdevices
    vstart 0 indicate the same device
    the other gfragment of the log apear seperate
    9 4 8704000 256000 768000 NULL 254992 Jun 7 2008 5:56PM 30

Posting Permissions

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