disk init resize mixed segment data and log

I am not sure what you are asking.  Can you be more explicit about what you are trying to accomplish and what Sybase is doing that you don't want it to.

Regards,
Bill

I have resized a device data with the command disk init resize.
The device segment has usage data and log but not data only.
How can I change that ?

Thanks

Ah, I see.  You have a mixed usage device that you want to change to just data and, I presume, you will create a different device for the log.

The short answer is there is no direct way to do that as far as I know.  There are some outlying exceptions and tricks but once you have a device in a mixed mode, you are stuck with it that way.

I presume that this is the only device for the database.

The best way is if you could do a dump and restore to a new incarnation of the database with the devices configured the way you need them.  Unfortunately, I am not sure how to do that or even if it is possible.  Sybase is typically pretty picky about wanting to restore into a segment and device configuration similar to what it was dumped from.

Joe Woodhouse might be able to shed some light on this.  Joe, you got your ears on?

You are probably going to have to create new devices for data and log the size you want them.  Then create a new database on them.  Finally you are going to have to migrate all of the database objects and data.

The easiest way to migrate the data is to use SELECT/INTOs.  Of course, if you have identity columns, that is not going to work so you will have to do it the slightly harder way with BCP Out and IN.  You could easily write a query to create all of the SELECT/INTO statements you need to move all the user tables in your database.  Something like the snippet below...

Of course, if you use identity columns on a few tables, you can smarten that up even more and do a left outer join to SYSCOLUMNS to find and eliminate those with idenity properties.

Sorry.  Maybe somebody has a way to fool the dump/load into doing what you want but I have not found documentation for it.

Best of luck,
Bill

SELECT 'select * into new_db..' + so.name + ' from src_db..' + so.name
  FROM src_db..sysobjects
 WEHRE type = 'U'

Select allOpen in new window

I think your issue is not with the device, but mixed usage of that device. To separate data and log devices you should use sp_logdevice.

If you don't have a log only segment, create one:

alter database <db_name> log on <log_device_x> = <size>

Then make database in single user mode:

use master
go
sp_dboption <db_name>, 'single', true
go
use <db_name>
go
checkpoint
go

After then

execute  sp_logdevice <db_name>, <log_device_x>

Hi Bill,

I just saw your comment now after sending mine. Yes, it is possible to do it without extending the database as well. It requires dumping database, recreating database with the separated data and log segments and loading from dump file. But, I think it is a more complex way.

Bibi92,

If you send the output of "select * from sysusages where dbid = db_id('<db_name>')", I can tell you whether you can use that method.

Joe had suggested another method based on mirroring as well.

Hi,

Ok, I send you that tomorrow.

Thanks

I'm still not sure I understand the issue here. Data and log being mixed has nothing to do with disk resize... [very confused]

It's actually pretty easy to un-mix mixed data & log for a database. Remember the devices don't know or care what's been stored on them. Objects also don't know or care which device they're on. Data vs log separation happens in segments which are not actually containers of data, they merely label fragments (or parts of a database that has been allocated to a device).

Confused yet? There is a many-to-many relationship between devices and databases, and between fragments and segments. If that weren't confusing enough, data & log might be logically separated in different fragments, but still be physically mixed in different fragments on the same segment.

Eg 1:

create database example1 on mixed_device_001 = 1024
go

This is a database with one fragment on one device. Data & log are logically mixed on that fragment, and physically mixed on that device. We cannot separate data and log here without creating another fragment, ie we must run alter database to enlarge this database. Note that we are not resizing the device!!


Eg 2:

create database example2 on mixed_device002 = 512
log on mixed_device_002 = 512
go

This is a database that does not have data & log mixed on any fragment. You can do transaction dumps of this database. However data & log are physically mixed on the same device, so if you lose that device, you lose everything. We can fix this without altering the database to make it larger.


I'm not sure why we're talking about dump & load, rebuilds or disk mirroring. To "unmix" data & log, we just have to do two things:

1) Make sure all new allocations from this point on for data & log will not be mixed.
2) Separate data & log that are currently mixed.

alpmoon has already given you one answer to (1): you use sp_logdevice to say "from now on, I want all new pages in my transaction log to go to this device. If that device has more than one fragment, all of those fragments will now be log devices. Probably not what you want.

Another method for achieving (1) that is more risky (you will break your database if you get this wrong) is to edit the "segmap" column of sysusages for one or more rows of your database. This allows you to define what all future allocations will be on this fragment (or fragments). This gives you very precise control over how things will be from now on, but it involves hacking important system tables. I'm reluctant to tell you more about this until we know what your database looks like. Let's just say for now we'll be able to walk you through what has to be done when we know more.

(2) is a bit more complicated, and the answer depends on whether you are trying to move transaction logs off a fragment you want to be data-only from now on (this is easy), or if you're trying to move data off a fragment you want to be log-only from now on (harder).

To move existing log records from a mixed data & log, we first have to make sure new log records have somewhere unmixed to go (part 1 above). Once this is fixed, we can exploit the fact that the transaction log is constantly being added to (by new transactions) but also constantly being removed from (every time we truncate it, by any of the means that truncate it). So all we have to do is push a whole pile of transactions through the logs, regularly truncating it, and eventually the log will entirely be on the new log-only fragment.

It's more complicated to move existing data from a fragment, but there are ways. Again I'd prefer to not make this reply any more complicated than it needs to be so I'll hold off on that until we know for sure that it's needed.

For alpmoon:

1> select * from sysusages where dbid =4
2> go
 dbid   segmap      lstart      size        vstart      pad    unreservedpgs
         crdate
 ------ ----------- ----------- ----------- ----------- ------ -------------
         --------------------------
      4           3           0     1792000    33554432   NULL          1782
                Apr 11 2008  6:37PM
      4           3     1792000     1792000    50331648   NULL           850
                Apr 11 2008  6:37PM
      4           3     3584000     1792000    67108864   NULL           741
                Apr 11 2008  6:37PM
      4           3     5376000     1792000   100663296   NULL            88
                Apr 11 2008  6:37PM
      4           3     7168000      512000   117440512   NULL           152
                Apr 11 2008  6:37PM
      4           4     7680000      716800   117952512   NULL        714000
                Apr 11 2008  6:37PM
      4           4     8396800      370688   201326592   NULL        369232
                Apr 11 2008  6:37PM
      4           3     8767488      192512   201697280   NULL          1958
                Apr 11 2008  6:37PM
      4           3     8960000     1087488   134217728   NULL           648
                Apr 11 2008  6:37PM
      4           4    10047488      485376   135305216   NULL        483480
                Apr 11 2008  6:37PM
      4           4    10532864      538624   150994944   NULL        536528
                Apr 11 2008  6:37PM
      4           3    11071488     1960960   151533568   NULL         34008
                Apr 11 2008  6:37PM
      4           3    13032448     2499584   167772160   NULL           964
                Apr 11 2008  6:37PM
      4           3    15532032      524288   184549376   NULL           102
                Apr 11 2008  6:37PM
      4           3    16056320      563200   118669312   NULL             0
                Apr 11 2008  6:37PM
      4           3    16619520     1024000   218103808   NULL             0
                Apr 11 2008  6:37PM
      4           4    17643520      524288   234881024   NULL        522240
                Apr 11 2008  6:37PM
      4           3    18167808     1048576   369098752   NULL          9099
                Jun 16 2008 10:25AM
      4           3    19216384     1048576   419430400   NULL           518
                Oct  9 2008  9:25AM
      4           3    20264960     1048576   452984832   NULL        744807
                Dec 15 2008 10:43AM
      4           7    21313536      768000   119232512   NULL        765000
                Feb 24 2009  3:32PM
      4           7    22081536      219136   135790592   NULL        218280
                Feb 24 2009  6:35PM
      4           7    22300672      768000   136009728   NULL        765000
                Feb 24 2009  6:58PM

(23 rows affected)
Thanks

OK, so it's only the last 3 fragments that have data & log mixed (segmap 7). Which of these do you want to be data-only, and which log-only?

Joe,

Some other data and log segments might have been created on the same disk device as well. If there was no 7, it would have meant that we could recreate the database without creating any data and log segments on the same device. But the last three segments are mixed themselves. So it is not an option here.

I think in this case using sp_logdevice is the best option and there is no need to create a new log device.

Bibi92,

Is it a production db? Or dev db loaded from somewhere else? Also sp_helpdb output would be helpful as well.

alpmoon: yes, agree, we don't need any new devices here. We can pick one or two to be data, and two or one to be log, and move things around accordingly.

sp_logdevice is part of the answer here but not all of it, as we could still have data pages on the new log-only device. We'll need some way of moving them off.

Easiest (once the segmaps are correct) is to identify all tables on the device (dbcc usedextents), bcp them out, truncate them, bcp them in. If they are APL tables with clustered indexes we could drop & recreate the clustered indexes (drags the APL table onto the segment the index is being created on).

As much as I see there is plenty of log space. I think what we need is only to eliminate log segments in the last three fragments and also in any other mixed segments if they exists. That is the reason I think sp_logspace may fix the problem without much more complication. I don't expect that any table would need to be relocated to another device.

However, if the database has been loaded by using a dump file from another database with a very different segment map or if data segments have been created in all log devices involved, it would be complicated. I think "sp_helpdb <db_name>" output would make the picture clear. But, even in such a case, I think combination of "dump, drop, recreate, load database" and sp_logdevice could fix the problem without bcp out/bcp in.

Ah, yes. Good point, if we're just removing the logsegment from those last devices, it's pretty easy.

Hi all,

It's a prod db

Thanks

Regards