Data and Log Files on the Same Volume

The usage pattern for database data and log files is very different, and having them on separate physical storage volumes could give a performance boost; but not always.

⚠ Virtual disks may reside on the same physical disks, and opting for multiple small volumes in the cloud could result in fewer IOPS when compared to a single large volume.

Suggested Action

After reviewing the storage layer and technology, consider moving the database files so that the log and data files are on separate volumes.

Script to List the Location of SQL Server Data and Log Files

SELECT DB_NAME(mf.database_id) AS DatabaseName,
       mf.name AS FileName,
       mf.type_desc AS FileType,
       mf.Physical_Name AS FileLocation
FROM sys.master_files mf
ORDER BY mf.database_id,
         mf.type_desc;

Further Reading

​Move Database Files | Microsoft Docs

Optimizing Transaction Log Throughput | Simple Talk