asked on

How to unmix data and log segments on same device SYBASE??

We recently have a disk crash, where we were running our SYBASE ASE 12.5 server, we had the dump files for databases and we could recover in other installed server, the issue is found out that we have the log and data spaces mixed on the same devices and we now have to be able to make dump of the transaction log, so I have to questions:

1. Is it possible to make dumps of the transaction under this database structure?
2. How does this strcuture affects the performance?
3. What detaled procedure should we follow in order to umix the log and data segments on same device?

I paste results for sysusages and helpdb:

dbid segmap lstart size vstart pad unreservedpgs crdate
------ ----------- ----------- ----------- ----------- ------ ------------- ---------------------------
4 3 0 12800 33554432 (null) 0 Apr 7 2009 7:00AM
4 4 12800 3840 50331648 (null) 3825 Apr 7 2009 7:00AM
4 3 16640 28160 50362368 (null) 0 Apr 7 2009 7:00AM
4 3 44800 64000 67108864 (null) 24058 Apr 7 2009 7:00AM
4 3 108800 6400 570425344 (null) 6375 Apr 7 2009 7:00AM
4 4 115200 3840 570476544 (null) 3825 Apr 7 2009 7:00AM
7 3 0 25600 167772160 (null) 0 Apr 7 2009 6:26AM
7 4 25600 6400 184549376 (null) 6375 Apr 7 2009 6:26AM
7 3 32000 25600 184600576 (null) 0 Apr 7 2009 6:26AM
7 3 57600 64000 201326592 (null) 0 Apr 7 2009 6:26AM
7 3 121600 64000 218103808 (null) 0 Apr 7 2009 6:26AM
7 3 185600 6400 234881024 (null) 0 Apr 7 2009 6:26AM
7 4 192000 6400 234932224 (null) 6375 Apr 7 2009 6:26AM
7 3 198400 51200 234983424 (null) 0 Apr 7 2009 6:26AM
7 3 249600 12800 251658240 (null) 0 Apr 7 2009 6:26AM
7 4 262400 25600 251760640 (null) 25500 Apr 7 2009 6:26AM
7 3 288000 25600 251965440 (null) 0 Apr 7 2009 6:26AM
7 3 313600 64000 268435456 (null) 0 Apr 7 2009 6:26AM
7 3 377600 64000 285212672 (null) 0 Apr 7 2009 6:26AM
7 3 441600 102400 301989888 (null) 40020 Apr 7 2009 6:26AM
7 3 544000 15872 318767104 (null) 15810 Apr 7 2009 6:26AM

name db_size owner dbid created status
------------------------ ------------- ------------------------ ------ -------------- ------------------------------------------------------------------------------------------------------
cobis 1860.0 MB sa 4 Apr 01, 2009 select into/bulkcopy/pllsort, trunc log on chkpt
device_fragments size usage created free kbytes
------------------------------ ------------- -------------------- ------------------- ------------------------------
cobis_dat 200.0 MB data only Apr 7 2009 7:00AM 128
cobis_dat1 440.0 MB data only Apr 7 2009 7:00AM 0
cobis_dat1 60.0 MB log only Apr 7 2009 7:00AM not applicable
cobis_dat2 1000.0 MB data only Apr 7 2009 7:00AM 364528
cobis_log1 100.0 MB data only Apr 7 2009 7:00AM 102000
cobis_log1 60.0 MB log only Apr 7 2009 7:00AM not applicable

--------------------------------------------------------------
log only free kbytes = 122512

name db_size owner dbid created status
------------------------ ------------- ------------------------ ------ -------------- ------------------------------------------------------------------------------------------------------
cob_cartera 8748.0 MB sa 7 Apr 01, 2009 select into/bulkcopy/pllsort, trunc log on chkpt
device_fragments size usage created free kbytes
------------------------------ ------------- -------------------- ------------------- ------------------------------
cob_cartera_dat 400.0 MB data only Apr 7 2009 6:26AM 0
cob_cartera_dat1 400.0 MB data only Apr 7 2009 6:26AM 0
cob_cartera_dat1 100.0 MB log only Apr 7 2009 6:26AM not applicable
cob_cartera_dat2 1000.0 MB data only Apr 7 2009 6:26AM 0
cob_cartera_dat3 1000.0 MB data only Apr 7 2009 6:26AM 0
cob_cartera_dat4 100.0 MB data only Apr 7 2009 6:26AM 0
cob_cartera_dat4 800.0 MB data only Apr 7 2009 6:26AM 0
cob_cartera_dat4 100.0 MB log only Apr 7 2009 6:26AM not applicable
cob_cartera_dat5 200.0 MB data only Apr 7 2009 6:26AM 0
cob_cartera_dat5 400.0 MB data only Apr 7 2009 6:26AM 0
cob_cartera_dat5 400.0 MB log only Apr 7 2009 6:26AM not applicable
cob_cartera_dat6 1000.0 MB data only Apr 7 2009 6:26AM 0
cob_cartera_dat7 1000.0 MB data only Apr 7 2009 6:26AM 0
cob_cartera_dat8 1600.0 MB data only Apr 7 2009 6:26AM 527104
cob_cartera_log 248.0 MB data only Apr 7 2009 6:26AM 252960

--------------------------------------------------------------
log only free kbytes = 611968

Any help would be really appreciated

Jan Franek

Ad 1
I'm not 100% sure but I think, that dump transaction shuld be possible

Ad 2
Log needs mainly sequential writes while data is access more randomly, so mixing them on one device will probably have some negative effect - mainly if you don't have some RAID cache on the way to disks. I have seen situations like this and I didn't see any visible slowdown so I guess that the performace impact will not be too big

Ad 3
To "unmix" data and log you need to 1 dump database, 2 drop database, 3 create database with correct order of data and log segments a then 4 load database. I suppose steps 1,2 and 4 are trivial, only step 3 is little bit tricky.

sp_helpdb output shows, that your cobis database has following structure:
640 MB data
60 MB log
1100 MB data
60 MB log

So you have to create it like this:
create database cobis on DATA = 640 log on LOG = 60
alter database cobis on DATA = 1100
alter database cobis log on LOG = 60

where DATA and LOG has to be replaced by your data and log devices. And if your devices are smaller then needed size, you can create it in more steps -

alter database cobis on DATA = 1100

can be replaced by

alter database cobis on DATA1 = 1000
alter database cobis on DATA2 = 100

ASKER CERTIFIED SOLUTION

Joe Woodhouse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jan Franek

OK, Joe is right, I'm not. I didn't notice that sysusages shows different order of data and log segments.

Joe Woodhouse

Hi Jan,

Yeah, I've been bitten by that before. It's easy to just look at sp_helpdb but it can be very misleading. Always go to the source rows in master..sysusages. :)

Veneno

ASKER

Thanks for the response I usually tried to be more clean in my process of dump and load of databases so the log and data don't get myxed on same device when you load. But like a year ago I was on vacations and a system crash occured I never get to know what the person who reinstalled did in detail and we have had the mixing since then, now we are trying now to implement an incremental bakcup plan and thought it was just about time to strar learning about this SYBASE intrincate.

TYVM