Question

State two reasons why it is advisable to place the transaction log on a separate disk device from the actual data?

Answer 1

Keeping transaction logs a separate disk

Placing the logs on separate disk:

1. Improves performance by reducingI/O contention
2. Ensures full recovery in the event of hard disk crashes on the data device.
3. Speed recovery, since simultaneous asynchronous prefect requests can read ahead on both the log device and the data device without contention.

Placing the transaction log on the same device as the data itself may cause reliability issues that both create database and alter database require the use of the with override option to out transaction log on the same device as the data itself.The log device can experience significant I/O on system with heavy update activity.

If your log and data are on the same database devices. The extents allocated to store log pages are not contiguous. Log extents and data extents are mixed. When the log is on its own device , the extents tend to be allocated sequentially.

Reducing disk head movements, thereby maintaining a higher I/O rate.