Choosing Where to Put Data and Log Files

The default file path is easy to accept during setup and hard to explain later. Choosing where to put data and log files should follow capacity, performance, and recovery needs.

Start With Workload and Storage

Data files store pages and indexes. Transaction log files record changes needed for recovery. Their I/O patterns differ, but modern storage does not obey every old rule about dedicated physical disks. Ask the storage team what the volumes actually represent. Two drive letters on the same shared array are not physical separation.

I inspect service-level goals and capacity before recommending paths. The right layout depends on throughput, latency, failure domains, backup staging, and operational ownership. A diagram of drive letters alone is too shallow. What happens if one volume fills or becomes unavailable? That answer matters more than whether a path looks tidy.

Check Current Defaults

SQL Server setup stores default locations for new data and log files. Review them before the first application database is created. A default that points to a system volume can create a capacity surprise later. Also inspect tempdb and backup locations, since they can compete with data files even when the application files are placed carefully.

The query below shows default data and log paths reported by the instance. Verify the actual folder and volume permissions on Windows. A path property does not prove the directory exists or has enough space.

SELECT SERVERPROPERTY(N'InstanceDefaultDataPath') AS default_data_path,
       SERVERPROPERTY(N'InstanceDefaultLogPath') AS default_log_path;

Inventory Existing Files

sys.master_files lists physical paths and allocated sizes across databases. Use it to find files on unintended volumes, identify growth settings, and plan migrations. Include system databases and tempdb in the review. Do not assume every file with an .ldf extension is the only log file for its database; read type_desc from the catalog.

I group the results by volume with infrastructure monitoring data. Allocated size is not the same as used space, and a volume hosts more than SQL Server files. Keep the actual free-space measure outside this query. The result is a map of SQL allocations, not a complete disk report.

SELECT DB_NAME(database_id) AS database_name,
       name AS logical_name,
       type_desc,
       physical_name,
       size * 8.0 / 1024 AS allocated_mb,
       growth,
       is_percent_growth
FROM sys.master_files
ORDER BY physical_name;

Separate Data and Log Files for a Reason

Placing data and log files on different storage can help when it separates failure risk or I/O contention in the actual architecture. On pooled or virtualized storage, different letters can still share the same devices and bottlenecks. Ask for the storage design and observed latency before prescribing a split. If the platform uses independent volumes with different protection, document those choices.

I have seen teams spend days moving files between letters that mapped to the same storage pool. The map looked improved. The workload did not notice. Start with a measured problem or a clear recovery requirement. Then choose placement that addresses it. A rule inherited from spinning disks should not overrule current evidence.

Leave Growth Room for Data and Log Files

Plan room for data growth, log spikes, index maintenance, backups, and emergency diagnostics. A log volume needs enough headroom for long transactions and failed log backups until the alert is handled. A data volume can need temporary room during maintenance. Set file growth increments deliberately and monitor the host volume, not only database allocation.

I ask who receives the low-space alert and how much lead time they need. A perfectly chosen path with no capacity process still fails. Keep a documented owner for volume expansion. Storage requests made after the volume is full are both urgent and avoidable.

Move an Existing Data File Safely

A file move needs a plan for availability, backup, exact logical name, target permissions, and rollback. For a user database, the usual pattern is short. Change the catalog path, take the database offline, move the file with Windows tools, then bring it online and verify. System databases and tempdb have different procedures. Follow the supported method for the specific file and deployment.

I do not offer a one-size-fits-all move script because the steps differ by database role. A copy to the new volume without a verified startup path can leave the database unavailable. Test the procedure on a restore or nonproduction copy and keep a current backup. Record the old path and exact file name before touching the filesystem.

Account for Backups and Restores

The file layout should be recoverable on the target server or have a documented WITH MOVE restore plan. A disaster recovery host can use different drive letters or mount points. Test a restore with the intended locations. Backup files themselves need capacity and access separate from database files where the architecture requires it.

I include path assumptions in the recovery runbook. During an outage, the restore operator should not discover that a target volume is missing only after the first restore command fails. Keep volume names, owners, and required capacity current. The best layout is one you can recreate under pressure.

Review Security and Operations

Grant the SQL Server service account the permissions needed on file directories. Avoid broad access for convenience. Monitor antivirus exclusions and backup agents according to supported guidance. Log growth, file initialization behavior, and storage snapshots can affect operations. Coordinate those choices with infrastructure teams rather than changing only the SQL configuration.

I check that monitoring labels volumes by purpose, not just letter. A DBA can then tell whether an alert concerns data, log, tempdb, or backups. Clear labels reduce response time. A small amount of documentation prevents the next person from treating every full disk as the same problem.

Revisit Data and Log Files After Workload Changes

A new reporting workload, retention rule, or availability design can change the best placement. Review file paths and growth trends after major application releases and storage migrations. Keep the default paths aligned with the current plan so new databases do not quietly return to the old volume.

Which file will grow first under the next large load? Your layout and alerts should answer that. A path is a decision about performance, capacity, and recovery. Make it visible, testable, and easy for the next DBA to maintain.

Related reading on this blog: How to Move Log File or MDF File in SQL Server? Interview Question of the Week #208 and Available Free Space in Data and Log File.

A file path is not a performance guarantee, it is an operational choice tied to real storage.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.