You are able to take transaction dumps of this database.
Your data and log are physically mixed, in that data fragments and log fragments appear on the same Sybase devices, but they are not logically mixed, in that no fragment has both data and log on it. (You can see this confirmed in sysusages - you have no fragment with segmap & 7 = 7 , ie. logical OR of bitmap 3 and bitmap 4.)
That's all that's required for dumps of the transaction log (ie incremental backups). If your recovery targets for this database are "recover to last incremental backup" then you already have everything you need.
Physical mixing does cost you the opportunity to perform up to the instant recovery. The idea with this is that so long as your log device(s) are intact, you can survive the loss of one or more data devices. Of course, if you aren't especially protecting your log devices then it's up to pure chance as to whether a disk crash kills data vs log.
To fix your physical mixing is simple but possibly tedious. The easiest (not necessarily fastest) method is to drop & recreate the database correctly - this is why they got mixed in the first place, you didn't create the database correctly when you recreated it.
The idea is when you load a database the pages must be written out in the order they appear in the database (ie in order of sysusages.lstart). Let's look at the rows from sysusages for dbid 4:
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
We're interested in segmap, lstart and size. Ordering by lstart (which it is) gives us the order the database fragments were originally created. Segmap tells us data or log. Size is in pages, and you seem to be using 16Kb pages so we divide size by 64 to give us a size in Mb.
That tells us dbid 4 was created as:
data = 200Mb
log = 60Mb
data = 440 Mb
data = 1000Mb
data = 100Mb
log = 60Mb
Note this is not the order reported by sp_helpdb! sp_helpdb is useless for this - it sorts the fragments in alphabetical order of device name!! You must go by sysusages ordered by lstart.
So all you have to do is dump your database, drop it, and recreate it to match the above. Remember you can only combine fragments in a single CREATE or ALTER DATABASE statement for consecutive data fragments up to the first log fragment, and then consecutive log fragments up to the next data fragment. Eg
create database cobis
on cobis_dat = 200
log on cobis_log1 = 60
go
alter database cobis
on cobis_dat1 = 440
, cobis_dat2 = 1000
, cobis_dat1=100
log on cobis_log1 = 60
go
The moral of the story is you cannot properly recreate a database unless you know what its original sysusages looks like. But! If you get it wrong (or you simply don't have access to it), you can always reconstruct what it is supposed to look like with a similar method to what I've used above.
This is a common DBA mistake, everyone is always confused about why this happens. Think of a database backup as writing out all the database pages one by one in the order they exist in the original database. When you load the database, Sybase says, ok, I see a data page in the backup so that's what I have to write now. In fact I see 200Mb of data pages, so that's what I'll write. And the next page is... uh oh, a log page. But the database I'm loading into says the next page gets written into a data device! Oh well, it can't be helped, the next page is a log page so that's what I have to write, even though that means I'm putting a log page on a data device.
At least in your case it was able to cleanly divide the fragments so data and log were not mixed on any one fragment.
It's a good idea when you take your database backups to also reverse-engineer the SQL needed to recreate them. That way you never ever run into this mess again. There are any number of scripts to do this, have a look on Sybase CodeXchange (needs registration but is free), or there is the generally good dbschema.pl Perl scripts. (They don't handle everything correctly but they get devices and databases right.)
Good luck!
Main Topics
Browse All Topics
by: Jan_FranekPosted on 2009-04-17 at 01:43:43ID: 24165804
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