Can Database and transaction logs on the same drive cause problems?

Question

Can we have the database and transaction logs on the same drive? What will be its consequences if it is not recommended?

Answer 1

The only downside is that it causes more thrashing on the disk, so worse performance.

A single write will require 2 seeks (between: write transaction log, write data, commit log). Having the transaction log on a separate disk means as few as zero seeks, because the drive heads can remain on the transaction log and the data.

Answer 2

The problem with having both on the same drive is that if the drive fails you lose both.

If they are on different drives and the drive containing the data fails you can apply the log to the last backup so you don't lose any data.

Answer 3

An company I worked for earlier had transaction logs and datafiles side by side on the same drive, in the same folder on several servers.

This didn't cause any problems datawise.

As others have noted it may well have impact on performance. And if you lose the drive you lose both.

Answer 4

In some scenarios you don't need transaction log at all. In that case you can switch database to Simple Recovery Mode and you gain performance and simpler administration benefits.

Answer 5

Just to add briefly to Ted Percival's comment above...

A hard disk drive will perform fastest if it is doing sequential writes or sequential reads, because the drive head doesn't need to move around.

SQL Server log files happen to be sequential, so if you dedicate a hard drive to ONLY the logs, you will see a noticeable performance improvement. That said, for smaller databases where performance is not an issue, it doesn't matter.

And as for Nir's comment on drive failures -- hopefully you are handling that at a lower level, by putting both your data and logs on RAID arrays.