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

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

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

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

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?

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

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

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

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