Detach logs and data files in Sybase database

Detailed steps, tested successfully

1, backup database, including master and you want to separate data and log application library, preferably backup all databases;

2, check the database log whether there is a separate storage equipment, if so, directly to the 5th step;

3, if there is no separate log device, then add a device: Disk init ...;

4, ALTER DATABASE db_name log on new_log_device=xxx;

5, Sp_logdevice Dbname,new_logdev (mobile log equipment);

6, Sp_dropsegment logsegment, db_name, device_name (if the database has more than one device to put both data and log, you should run several times;

7, create a temporary table, and then insert enough data into the inside, and then truncate the log;　　 use db_name
　　 go
　　 create table t1 (id int)
　　 go
　　 declare @loop int
　　 select @loop = 1
　　 while(@loop<500)
　　 begin
　　 insert t1 values(@loop)
　　 select @loop = @loop + 1
　　 end
　　 go
　　 dump tran db_name with truncate_only
　　 go

8. To this end, data and log separation has been completed, using sp_helpdb db_name or sp_helplog to see if it has been detached.

Adding and removing segment does not move the current allocated space. The log has at least one extension (extend) on the previous segment (remember, when allocating a storage unit to an object, it is actually extend.) )。 If the current extend is filled and needs to be allocated for the log, ASE is allocated on the new segment (segment constraints it has to do). At this point, truncating the log can reclaim the previously allocated extend. Finally, you want to back up all the databases.