If you did not use the log on clause to create database, follow the instructions in this section to move your transaction log to another database device.
sp_logdevice moves the transaction log of a database with log and data on the same device to a separate database device. However, the transaction log remains on the original device until the allocated page has been filled and the transaction log has been dumped.
If the log and its database share the same device, subsequent
use of sp_logdevice affects only future
writes to the log; it does not immediately move the first few log
pages that were written when the database was created. This creates
exposure problems in certain recovery situations, and is not recommended.
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.
To move the entire transaction log to another device:
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 will 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 put the database into single-user mode with sp_dboption.
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 will be removed. See Chapter 27, “Developing a Backup and Recovery Plan,” for more information.
Run sp_helplog to ensure that the complete log is on the new log device.
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.
Transaction logs are discussed in detail in Chapter 27, “Developing a Backup and Recovery Plan.”
