Sybase - How to get logsegments off my data devices?

Question

I'm running Sybase ASE 15.7 and coming up to speed to clean up a database server installed by a non-DBA.

I have a database that has the following device configuration:

I want to have data1 and tlogs1 devices hosting only Data and Log respectively so that I don't encounter any anomalies in the event of a database restore due to a device failure.

How can I correct this configuration?

Note: The remove button is always grayed out.

Answer 1

I was unable to fix the database in place, but I did manage to solve this misconfiguration by:

• Creating a new database with my optimal device layout
• Migrating the schema via DDL generation scripts using ddlgen.bat
• Using bcp to migrate the table data

Note: You could use Sybase Central's DDL generator, but it doesn't include table's indexes for some reason.

When done, I have well documented database schema scripts to place in source control.

Answer 2

The only real way to solve this is, as I think you discovered, is to create a new database (or drop and re-create the existing database). You don't need to do anything with DDL or bcp though.

When you run DUMP DATABASE the output is exactly as it would be inside the database. Looking at the output of sp_helpdb and sp_helpdevice should give you a rough idea of how the database is structured. The sysusages table in the master database is the best place to look at the database structure:

select * from master..sysusages where dbid=db_id(mydb) order by lstart

The segmap column specifies whether the segment is data, log or data+log.

Once you've examined the output of all of these, you can re-create the database with the correct segmaps:

CREATE DATABASE mydb ON
data1=80
LOG ON
tlogs1=20,
tlogs1=20

If your database had been structured as a mix of something like data, log, log, data, log (i.e. it had been expanded several times with ALTER DATABASE) then you could use bcp to copy out all tables and re-create the database in a contigous order.