Moving the Transaction Log to Another Device

If you did not use the log on clause to create database, you can move your transaction log to another database device.

sp_logdevice marks the portions of an existing database that exist on a specified device as reserved for the transaction log; it does not move existing data. If your database already has data on this device, SAP ASE does not interpret this data as not being on its proper segment. However, because dbcc reports this as an error, no existing part of the log moves to the specified device; the current log data remains where it is until the log has extended onto the new device and you use dump transaction to clear that part of the log. Also, sp_logdevice does not allocate new space to the database or initialize devices. Instead, it reserves those portions of the specified device for the log that already belong to the database you specify.

The syntax for sp_logdevice is:

sp_logdevice database_name, devname

The database device you name must be initialized with disk init and must be allocated to the database with create or alter database.

Execute sp_logdevice, naming the new database device.
Execute enough transactions to fill the page that is currently in use. The amount of space you need to update depends on the size of your logical pages. You can execute dbcc checktable(syslogs) before and after you start updating to determine when a new page is used.
Wait for all currently active transactions to finish. You may want to use sp_dboption to put the database into single-user mode.
Run dump transaction, which removes all the log pages that it writes to disk. As long as there are no active transactions in the part of the log on the old device, all of those pages are removed.
Run sp_helplog to ensure that the complete log is on the new log device.

Note: When you move a transaction log, the space no longer used by the transaction log becomes available for data. However, you cannot reduce the amount of space allocated to a device by moving the transaction log.
If required, run dbcc findstranded(database_name) to change the database status from "mixed log and data."