I'm trying to migrate an existing database to a newly created one on a higher performance sever. I have new device mapping allocations for performance reasons. In particular, I am trying to map the log to its own device. Since, the device mappings are different between the old and new databases, (I have found) Adaptive Server is mixing the log and data on the same device. As a result, I am unable to keep all the log device space usable for the log.
Can anyone offer a solution to this?
Thanks, in advance.
First some explanation :
When you load a database from dump, sybase initialises the device fragments for usage by the database in the same sequence as the original. So, if you had 1000 data + 200 log + 250 data + 200 log + 100 log, in the original database, that's what sybase will do for the target db.
At best, u can merge multiple fragments into one so u could have
1000 data + 200 log + 250 data + 300 log
Now a suggestion :
You could use sp_logdevice to move the log to a different device & then follow it up with a sp_dropsegment to stop utilising a device for log. Note the new device is used for the log on for future use. So, you can't drop the logsement immediately. You need to wait until it start utilizing the new log device & then you do a dump tran to clear the log.
I would suggest you try this on a test database if you haven't done this before. Once you are comfortable with the steps, do it on your live database.